Leta i den här bloggen

2010-08-20

Är du en ofrivillig MS SQL Databasadministratör? (del 2)

Denna post är del 2 i en serie. Här är samtliga delar i denna serie:
del 1 - Data och Logfils hantering
del 2 - Index, Statistics
del 3 - Korrupt data
del 4 - Backupper
del 5- Sammanfattning

Igår skrev jag om den ofrivillige DBAn och om storlekar på databas-filer.. Idag tänkte jag fortsätta med detta. Målet är inte att få den perfekta MS SQL miljön utan en miljö som är "tillräckligt bra".

Jag tänkte skriva ned några av mina tankar kring detta. Observera att det inte finns någon inbördes ordning bland dessa tankar utan alla sakerna är sådant som man bör fundera på....

Index
Vad är ett index? Enkelt uttryckt kan säga att det är nått som används för att det skall gå så fort som möjligt att hitta information i databasen. Tänk exempelvis på en telefonkatalog. Om alla personer stod i slumpvis ordning skulle det bli ganska jobbigt att hitta rätt nummer. Därför är alla telefonnummer ordnade efter namnet på personen som har telefonnnummret (eftersom att det vanligast är så vi letar efter informationen i en telefonkatalog). Detta är en typ av index (ett så kallat "klustrat index")
I en bok har man ofta kapitel och det brukar finnas en indexsida i början som beskriver på vilken sida som vilket kapiten börjar. Detta gör att om jag tittar i indexet i boken "Inside Microsoft SQL Server 2005: The Storage Engine" och vill läsa om "Index Internals and Management" så kan jag direkt bläddra till sidan 249 och slipper titta på alla sidorna i boken för att hitta rätt.. Detta är en annan typ av index (ett så kallat "icke-klustrat index")
Igår talade vi om fragmenterade filer. Även databaser kan bli fragmenterade internt inuti filen. Både själva datat i databasen men även index kan bli fragmenterade.
Det finns två huvudtyper av fragmentering i datafiler:
  • Fragmentering inom data- och indexpages (kallas även för "internal Fragmentation")
  • Fragmentering inom index eller tabellstrukturen som innehåller data pages (kallas även för "logical Scan fragmentation" eller "Extent scan fragmentation")
 Men vänta nu? Vad är detta "Data Pages" som dök upp helt plötsligt? Jo, allting som sparas i en datafil sparas på "data pages" (jag har ingen bra översättning. "Data sidor" låter så fel...).
En Data page är alltid 8 kB stor (=8192 bytes). När SQL server läser data från hårddisken så läser den alltid en hel data page i taget. Därför bör man försöka se till att "Allocation unit size" är 8 kB (eller mindre). Om man har en Allocation unit size som är större så tvingar man windows att läsa mer data från disken än vad den behöver vid varje läsning, detta påverkar prestandan negativt.
En data page innehåller alltid en header på 96-bytes. Detta gör att det finns 8096 bytes kvar för SQL-server att spara information på.
Om man har en tabell med bara en kolumn och datat som skriv in på varje rad är på 4500 bytes så kommer det bara att få plats en sådan cell per data page. Vi får då 3596 bytes med tomt utrymme på varje datapage.
Denna typ av fragmentering är ruskigt svårt att påverka. Det är en fråga för applikationen som använder databasen om man kan ändra så att datat sparas på annorlunda sätt.
En vanliga orsak till intern fragmentering är att det sker förändringar på datat. Helt enkelt genom att det läggs till data, data tas bort eller att befintligt data ändras.  Alla dessa saker kan skapa tomt utrymme på en data page.
Normalt sett har man en "Fill factor" på data pages. Om man har en fill factor på 80 så betyder det att man lämnar 20% av varje data page när man skapar ett index eller kör en rebuild på indexet.

En "logical scan fragmentation" skapas normalt av en så kallad "page split". Detta är något som normalt inträffar när man lägger till data på en data page och datat inte får plats. Exempelvis: vi har en data page som har 7500 bytes med data sparat just nu. Vi gör ändring av datat i en av "cellerna" i tabellen och lägger till 700bytes med data. Eftersom 7500+700= 8200 är mer än 8096 så får all denna information inte plats på den data pagen.
Här genomför SQL storage engine en "Page Split" och flyttar ungefär hälften av datat till en ny data page. Jag säger "ungefär" eftersom att det beror på hur storleken på "cellerna" som är sparade på den data pagen. Det flyttas alltid hela celler och inte delar (någor förenklat i vissa specialfall, men låt oss säga att det är så...). Därefter läggs datat till i den nya data pagen.
Eftersom att databasen med stor sannorlikhet består av många data pages så kommer den nya data pagen med stor sannorlikhet inte att hamna bredvid den gamla. Vi har alltså en fragmentering i databas-filen där två pages som "hör ihop" inte ligger bredvid varanadra och detta gör att läshuvudet måste flyttas mer på hårddisken. Vi har alltså en intern fragmentering i databas-filen. Även om man gör en defragmentering i Windows så kommer den interna fragmenteringen vara kvar.

Ett bra sätt att se till att man inte får (så många) fall av fragmentering är att se till att man har en bra fill factor på sina databaser. Dock är det i princip hopplöst att veta vad en "bra fill factor" är utan att göra en massa analyser.
Normalt i är fill factor ställt till 0 (som är samma sak som att 100) vilket ger att appa data pages får fyllas till 100%. (se mer info här om fill factor)
Man bör vara ganska säker på vad man gör om man skall ändra denna. Ju lägre fill factor desto mindre risk för page splits, men samtidigt så måste storage engine läsa fler pages för att komma åt samma mängd data. Eftersom att det normalt sett är mycket mer läsningar från en databas än uppdateringar så slår en försämring i läsningshastighet mer än en försämring i skrivningshastighet.
Om det är så att uppdateringar sker i hela databasen (inte bara i slutet) så kan det vara värt att ändra fillfactor till ett annat värde än 0.

"Men hallå! Nu har jag ju läst en massa text och sedan så säger du att man inte skall ändra denna ändå? Ge tillbaka den tid jag har förlorat!!" kanske vän av ordning ropar nu.
Ja, men det är inte så enkelt att en fillfactor löser alla problemen. Dock skall man säga att om man inte uppdaterar sina index (alltså kör maintenance jobb med rebuild eller reindex på databasen) så kommer SQL-servern ganska snart att sluta använda indexarna helt, eftersom att de inte stämmer... Tänk tillbaka på exemplet med indexet i boken tidigare, om man lägger till nya sidor i boken men inte uppdaterar indexet så måste man ju efter ett tag läsa igenom hela boken ändå för att hitta informationen och då kan man lika gärna låta bli att först kika i indexet...
Så se till att alltid ha ett jobb som uppdaterar indexarna med jämna mellanrum. Den enkla lösningen är att köra en reindex varje natt (eller när man nu har lågbelastning på servern). Dock är detta inte alltid en bra lösning. Speciellt inte om det är en väldigt stor databas, eller en databas som är hårt belastad hela tiden. Det är nämligen så att när man kör själva rebuilden av indexet så tas först indexet bort och då kommer databasen helt plötsligt bli väldigt långsam (i SQL 2005 Enterprise edition (och 2008) finns möjligheten att göra detta online, men Enterprise kostar något mer att köpa in...).
Om du vet med dig att du har en databas som uppdateras lite överallt så kan det vara en idé att kika på hur många page splits det sker och om det är många så ändra fill factor tills dess att antalet page splits går ned....


Statistics
Varje gång en SQL fråga körs på en SQL server så är det Query procesorn som ansvarar för att köra frågan. Innan själva frågan körs så kollar Query processorn på frågan och beslutar vilka tabeller som skall anropas och vilka indexar som skall användas. Som en del i beslutsunderlaget används statistik som beskriver hur datat är fördelat i kollumner och indexar. Självklart måste denna statistik vara korrekt för att beslutet skall bli korrekt. Det som Query processorn tar fram är vilken "query plan" som snabbast möjligt utför det som SQL frågan vill (antingen tar fram information, uppdaterar information eller raderar information i databasen).
I SQL Enterprise manager kan man högerklicka på en databas, välja properties och sedan under "options" så finns det "Auto Create statistics" och "Auto Update statistics". Dessa skall normalt vara satta som "True" men om man precis fått ärva en databas från någon annan så rekommenderar jag att man kollar denna inställning...
Det skall även sägas att varje gång man kör en rebuild på ett index så uppdateras även statistiken med en full scan av tabellen (dock gäller detta bara rebuld, om man kör en reindex på en databas sker ingen uppdatering av statistiken).

Kontentan av detta är att även statistiken bör uppdateras med jämna mellanrum.

Avrundning:
Det finns en herre som heter Ola Hallgren som har skrivit ett helt underbart verktyg som kan gå in och titta i databaser, indexar och statistik och själv besluta vilken typ av uppdatering som bör göras och vart.
Själva SQL-scriptet går att hitta här: http://ola.hallengren.com/ tillsammans med mängder av bra dokumentation och liknande..
Om man inte vill (eller kan) köra Olas script så rekommenderar jag att på en mindre databas köra reindex på databaserna minst en gång i veckan (gärna varje dygn om det är möjligt ur prestanda och schemaläggningssynpunkt, med tanke på backupschema och liknande)).
Är det en väldigt hårt belastad server med mycket uppdateringar i överallt i tabellerna och inte bara i slutet av tabellerna så kommer man troligen att få en massa page splits. I så fall kan det vara värt att sätta en annan fill factor, köra om reindex och kolla om det blev bättre...

Inga kommentarer:

Skicka en kommentar

Related Posts Plugin for WordPress, Blogger...