Wednesday, October 31, 2007

Database Ownern

Fick ett intressant fel när jag skulle välja egenskaper på en databas. En stor dialogruta sade att:

Property Owner is not available for Database '[DBNAMET]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

Efter lite antydningar från några artiklar på nätet så beror det på att OWNERN saknas på databasen. Efter att ha gjort en select på sysdatabases så såg jag att värdet var NULL. För att rätta till felet så är det bara att sätta en ny owner med SP_Changedbowner. Felet visar sig bara i 2005, inte i 2000 då man fortfarande kan välja egenskaper. Där emot blir kan man inte använda tex SP_Helpdb på databasen.

Kontentan utav detta är att se till att rätt user är owner på databasen efter installation. Det är lätt hänt att en utvecklare eller konsults konto blir borttaget och då få man dessa problem.

Wednesday, August 15, 2007

Partitions i SQL 2005

SQL 2005 innehåller en funktion som heter Partition vilket innebär att man helt enkelt dela ut en tabell eller index på flera filgrupper. Jag tänkte försöka förklara detta lite närmre här.

Låt oss säga att vi har en tabell som innehåller vanligt orderdata och en id kolumn. I detta exemplet så vill jag dela upp tabellen på id kolumnen. Order från 1 till 1000000 skall hamna i en fil grupp 1000001 till 2000000 skall hamna i filgrupp två osv och data över 3000000 i den sista filgruppen . Då börja vi med att skapa de fysiska databsfilerna och sedan själva filgrupperna. Vitsen med Partition är att sprida ut tabellen på flera ställen så att servern kan nyttja fler läsarmar i disksystemet. I detta fallet så skapade jag fyra st och lät dom heta DataFile1 osv.

Nu är det dags att skapa själva Partitionen (eller vad man nu skall kalla den på svenska).

Först funktionen:
CREATE PARTITION FUNCTION Function_tabellnamnet (INT)
AS RANGE RIGHT FOR VALUES (1000000, 2000000, 3000000)


Sedan Schemat:
CREATE PARTITION SCHEME Schema_tabellnamnet
AS PARTITION Function_tabellnamnet
TO (DataFile1, DataFile2, DataFile3, DataFile4)


Nu kan vi skapa själva tabellen:
CREATE TABLE Test (Id INT, OrderName VARCHAR(20), Date (datetime)) ON Schema_tabellnamnet (Id)

Nu är det bara att fylla tabellen med data och SQL kommer dela upp datat på flera filer och få möjlighet att läsa från fler ställen vilket förhoppningsvis kan öka prestandarden i systemet.
Vill man se hur datat är uppdelat mellan de olika partitionerna kan man göra det med följande kommando:
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('tabellnamn')

Eller:
select $partition.Function_tabellnamnet(id) as partitionNum, count(*)
from dbo.Test
group by $partition. Function_tabellnamnet (id)

Partition går även att använda på tex datum kolumner om det passa bättre. Kan vara bra om man tex vill lägga över gammalt data som inte används så frekvent till en annan disk. Finns en hel del möjligheter att pröva.

Monday, August 13, 2007

Medianvärde

Satt och klurade på att få ut medianvärdet från en pris column. Lyckades inte men efter lite googlande så hittade jag en fungerande sqlsats skriven utav Anjuna Moon i idg-s forum. För mig verka det fungera.

SELECT AVG(C1) AS Expr1
FROM (SELECT MAX(Price) C1
FROM (SELECT TOP 50 PERCENT Price
FROM [object]where type = 'värde'
ORDER BY Price) T1
UNION ALL
SELECT MIN(Price) C1
FROM (SELECT TOP 50 PERCENT Price
FROM [object] where type = 'värde'
ORDER BY Price DESC) T1) T3

Wednesday, June 13, 2007

Optimering utav SQL Server. Filgrupper

I en databasmiljö där man har mycket stora databaser finns en möjlighet att optimera SQLServern med Filgrupper. En databas består i grunden utav en Primary filgrupp där själva databasfilen ligger. Sen finns det en för transactionsloggen. Denna fil kan vi inte göra något med då loggfilen bara kan ha en filgrupp. Däremot kan databasfilen placeras i flera filgrupper. Varje filgrupp kan sedan ha flera filer under sig. Här kan man vinna en del performance genom att placera tabeller och index i olika filgrupper och sedan filerna på olika diskar. Nedan följer några tips att tänka på.

Tabeller som ofta JOINAS i en query skall inte ligga i samma filgrupp.

Om du har en tabell i databasen som du vet används mycket så överväg att placera den i en egen filgrupp på en egen disk. Då kan man dra nytta utav SQL-s förmåga att läsa datat sekventiellt vilket är mycket snabbare än vanlig läsning.

En tabell som används väldigt mycket kan dra nytta utav att placeras i flera filgrupper då datat kan läsas från fler ställen samtidigt. Kan man dessutom placera dessa filgruppers filer på egna diskar så skulle performancen bli ännu bättre.

På mycket stora databaser är backuper knappt hanterbara om man inte delar upp databasen i flera filgrupper.

Icke clustradeIndexen till en tabell kan läggas i en egen filgrupp.

Tempdb databasen som sköter alla sortering mm i SQL servern är lämplig att lägga i flera filgrupper för att då öka performancen.

Friday, May 18, 2007

Optimering utav SQL server. Index fragmentering

Hitta fragmentering i index

Vilka index skall vi leta fragmentering i? Liksom i den andra artikeln så bör vi titta efter index som används i tunga querys och som används ofta. Med hjälp utav SQL Profilern och den färdiga templaten SQLProfilerTSQL_Duration få vi fram en tracefil för vidare undersökning utav tabeller som kan vara lämpliga för en närmare analys.
I SQL 2000 används dbcc showcontig. I SQL 2005 finns det nya funktioner för att se denna information. Kör en SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks'), OBJECT_ID(), NULL, NULL , 'DETAILED');
I kollumnen avg_fragmentation_in_percent ser man fragmenteringen utav indexen, detta bör vara så nära noll som möjligt. Värdet avg_page_space_used_in_percent skall vara så nära 100 som möjligt eller så nära den fyllnadsgrad man valt på indexet. Med detta kommando få man ut massa information om indexet.

Ett exempel i sql 2000 kan se ut så här:

DBCC SHOWCONTIG scanning 'MIS_MIS_ART_SUP_HIST' table...
Table: 'MIS_MIS_ART_SUP_HIST' (1554104577); index ID: 1, database ID: 14
TABLE level scan performed.
- Pages Scanned................................: 8096
- Extents Scanned..............................: 1019
- Extent Switches..............................: 1018
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.31% [1012:1019]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.10%
- Avg. Bytes Free per Page.....................: 1551.1
- Avg. Page Density (full).....................: 80.84%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Här har vi då information om indexet på en tabell. För att det skall vara någon större idé att defragmentera så bör indexet generellt sett har mer än 1000 pages.

För att se om vi har en fragmetering utav indexet tittar vi främst på två värden. Avg. Page Density (full) som bör vara så nära den fyllnadsgrad som är valt på indexet. Är det skapat med 100% fyllnadsgrad så skall det liggar där omkring. I exeplet har vi just kört en dbcc dbreindex vilket optimerat indexet och fyllnadsgraden ligger nästan på 100%.

Logical Scan Fragmentation skall ligga så nära noll som möjligt. Går det över 10% börja det bli ett performance problem.

Med detta i tanken så är det lättare att avgöra om man skall defragmentera ett index eller ej. Att göra en sådan operation kan ta lång tid och mycket kraft från servern så behövs det inte så gör det ej.

Saturday, April 28, 2007

Optimering utav SQL server. Underhålla Indexen

Underhålla indexen
För att hålla indexen optimerade krävs att man frekvent kör optimeringsjobb för att indexen skall vara uppdaterade. Beroende på hur mycket som ändras och läggs till i en tabell så måste dessa jobb köras mer eller mindra ofta. Vilka index behöver då underhåll? Ett bra sätt att se detta är att kolla vilka querys som tar tid och används frekvent. Detta kan vi göra i SQL Profilern med hjälp utav templaten SQLProfilerTSQL_Duration. Querys som tar tid är ett bra alternativ för att se över indexen för.
I SQL 2000 kör vi dbcc dbreindex eller dbcc indexdefrag för att optimera indexen. Skillnaden mellan dessa två är att indexdefrag går att köra online, dvs indexet är tillgängligt under operationen. Dbcc dbreindex är att föredra om det är hög fragmentering i indexet och du kan även välja fyllnadsgrad.

Kommandon för SQL 2000:
DBCC DBREINDEX ('tabellnamn', '',80);
DBCC INDEXDEFRAG ('databasnamn', 'tabellnamn', 'indexnamn')


I SQL 2005 har man ersatt dbcc dbreindex och indexdefrag med Alter index och rebuild eller reorganize. Man har även möjlighet att göra rebuild online och att välja att sorteringen skall göras i tempdb, vilket kan vara bra ur prestandard synpunkt. Kräver dock att tempdb ligger på en egen diskyta annars gör det ingen nytta.

Kommandon för SQL 2005:
Alter index all (för alla index i tabellen)
On tabellnamnet
Rebuild with (fillfactor = 80, SORT_IN_TEMPDB = ON, ONLINE = ON);



Är avg_fragmentation_in_percent value mindre än 30% så välj REORGANIZE.
Alter index all (för alla index i tabellen)
On tabellnamnet
Reorganize;

Friday, April 20, 2007

Optimering utav SQL server. Index

Att skapa och identifiera nödvändiga index
När man skall se över indexen i en datatbas så fokusera i förstahand på tabeller med mycket data och som används frekvent. Dom andra spelar inte så stor roll. För att kunna optimera indexen så skall man vara medveten om hur databasen används och vilka frågor som är vanligast annars blir det svårt att göra något. Nedan följer ett antal punkter man skall ha i åtanke vid indexoptimering.

Överväg att Indexera alla kolumner där klausulerna WHERE, ORDER BY, GROUP BY, TOP och DISTINCT används ofta.

Ta bort index som inte används utav Queryoptimizern. Indexen tar bara onödig plats och prestandard om dom inte används.

Vilken fyllnadsgrad skall indexen ha. Är det bara läsning som sker mot tabellen så är en full fyllnadsgrad att föredra (Pading index) medan en tabell som det skrivs mycket till kanske skall ligga på 50%.

Som en tumregel så bör alla tabeller ha ett Clustrat Index. Generellt sett, men inte alltid, så ska det Clustrade Indexet finnas på en kolumn vars värden ökar monotont, såsom t ex identitetskolumner eller kolumner vars värden ökar – och är unika. I många fall så är primärnyckeln en ideal kolumn för Clustrade Index.

Returneras bara ett värde från tabellen så är det bättre med ett icke clustrat index.

Kolumner utav typen image, text skall inte ha några index.

Om man har två eller fler tabeller som JOINas ihop väldigt ofta, så bör de kolumner som används för JOINerna ha de korrekta Indexen.

Om möjligt se till att göra indexen unika, när databasmotorn hitta värdet behöver den inte fortsätta att söka igenom hela indexet för den vet att det bara finns ett unikt värde.

Index skall skapas på så smala kolumner som möjligt.

Ett index är ge bättre resultat på en kolumn med numeriska värden än tecken.

Index kan påverka olika operationer både positivt och negativt. Tex en query som gör en select går mycket bättre när man lägger till ett clustrat index. Men om samma tabell kanske fylls med data vid ett tillfälle per dygn och det då görs en massa inserts så kan samma index påverka denna operation mycket negativt. Vid detta tillfälle kan man låta droppa indexet före operationen för att sedan skapa det igen efteråt.

Thursday, April 19, 2007

Restore utav Microsoft SQL server 2000

Restore utav en SQL server kan vara lite knepigt. Här följer en instruktion som skall följas steg för steg. Restore kan göras till likvärdig filstruktur vilket är enklast eller till en helt olik filstruktur på en annan server vilket är lite mer kompext. Nedan följer först den enkla varianten sedan den till en annan filstruktur.
Första stegen är att installera upp en ny server med samma konfiguration, tex namn, nätverksinställningar och liknande. Observera att det är viktigt att diskar och partitioner blir likadant konfigurerat som på den servern man skall efterliknar annars kan det bli lite strul när man återläser systemdatabaserna för SQL. Djupare beskrivning utav detta kan läsas senare i dokumentet.



Återställning utav Operativsystemet och servern

Installation av Operativsystem inklusive uppdateringar på en helt ny server med likvärdig konfiguration, så som Tex att disk konfigurationen blir rätt.

Installation av MS SQL Server inklusive samma SupportPack nivå som innan. Collation mm kvittar då dessa inställningar finns i Master databasen som skall läsas in från backup senare.

När servern är installerad så skall SQL servern återställas så att SQL få sin ursprungliga konfiguration.

Återställning utav SQL servern

Tänk först på att stänga av övervakningsagenter och liknande innan man går vidare så att inte de håller på att starta SQL.

Backupdatat återläses från backupmedia till den nya servern till en lämplig katalog.

Börja med återläsning av Master databasen. Detta görs genom att starta SQL Servern i singleusermode. Stoppa först tjänsten och öppna sedan en kommandopromt.
Skriv Sqlservr.exe –c –m Välj sedan restore på Master databasen i EnterpriseManager alternativt i QueryAnalyzer. Fodras dock att man behärska Transact SQL kommandon. Peka ut sökvägen dit backupfilerna nu finns och genomför restoren. Databaserna hamnar nu på samma ställen som de ursprungligen fanns.

Efter restoren utav Master databasen så stängs SQL servern tjänsten av automatiskt. Starta upp den igen som vanligt och läs på övriga systemdatabaser på vanligt sätt.

Nu är servern installerad och konfigurerad som före systemkrashen. Nu kan datat för användadatabaserna läsas tillbaks.

Återläsning till annan filstruktur, flytt till ny server

För att återställa SQL server till en annorlunda filstruktur än ursprunget måste man göra enligt följande punkt för punkt lista. Det är viktigt att det görs exakt rätt. Här använder jag Queryanalyzern istället för Enterprise Manager.

Först starta man SQL via kommandopromten: sqlservr.exe -c –f
Nu är SQL igång och då ansluter man med Queryanalyzern till server. I den skriver man följande kommando för att restora. Observera att vi ändra sökvägarna till databasfilerna. Master restoras sen stoppas SQL automatiskt.

RESTORE DATABASE master FROM DISK='d:\sql\master.bcp' WITH
MOVE 'master' to 'c:\mssql\data\master.mdf',
MOVE 'mastlog' to 'c:\mssql\data\mastlog.ldf',
REPLACE
GO


Efter att master databasen är restorad så startas SQL från kommandopromten igen som innan fast med ett par andra parametra. Skriv följande: sqlservr.exe -c -f -T3608 -(T3607) -T4022
T3608 kommandot säger till SQL att starta utan någon annan databas vilket ger oss möjlighet att ändra Tempdb sökvägen. Informationen i Master databasen säger nu att den ligger på det gamla stället i filsystemet vilket vi måste ändra för att kunna starta upp SQL på ett riktigt sätt. För att ändra skriv följande i Queryanalyzern:

update sysdatabases SET filename = 'C:\MSSQL\Data\tempdb.mdf'
where name = 'tempdb'
GO


Även model databasens sökväg måste ändras annars kan vi inte fortsätta.

update sysdatabases SET filename = 'C:\MSSQL\Data\model.mdf'
where name = 'model'
GO


Sen måste den sökvägen till filerna i själva Tempdb ändras.

ALTER database tempdb MODIFY File (name = tempdev, Filename = 'c:\mssql\data\tempdb.mdf')
GO
ALTER database tempdb MODIFY File (name = templog, Filename = 'c:\mssql\data\templog.ldf')
GO


Nu kan man gå vidare med att göra restore utav de andra systemdatabaserna msdb och model. Stäng ner SQL och starta upp den normalt. Gör sedan restore i Quryanalyzern eller Enterprisemanager.

restore database msdb from disk = 'd:\sql\msdb.bcp'
with move 'msdbdata' to 'c:\mssql\data\msdbdata.mdf', move 'msdblog' to 'c:\mssql\data\msdblog.ldf', replace


Då är restoren klar. Om servern har fått ett nytt namn måste detta ändras. Eftersom man har restorat master databasen från ett annat system så står det gamla namnet kvar i vissa systemtabeller. Detta ändras med följande kommandon i Queryanalyzern:

exec sp_dropserver 'old name'
exec sp_addserver 'new name', 'local'


Nu är SQL Servern helt återställd och kan användas igen.

Frågetecken kring SQL Jobb

I samband med namnbyte på servern kan man få problem med att jobben inte går att editera efteråt. Detta beror på att det i tabellen sysjobs och columnen originating_server i MSDB databasen står det gamla servernamnet. Ändera detta till rätt servernamn.

UPDATE SYSJOBS SET ORIGINATE_SERVER = 'namnet'

Även i sysjobssteps tabellen och columnen server kan fel finnas. Bör vara NULL eller servernamnet men det nya namnet.