Att förstå SQL Serverns DBCC SHOWCONTIG
Förord
DBCC SHOWCONTIG är ett underbart verktyg som hjälper dig att förstå mer av ditt system än vad man ser vid första anblicken. Kikar vi på dokumentationen, så använder den ärligt talat inte direkt termer som gör det lättare att förstå heller. Så den här artikeln kommer att fokusera på några av grundtankarna med att använda SQL Server, och hur du kan använda det för att bättre förstå vad som pågår inne i din server.
SQL Server datalayout
Ett av de allvarligaste prestandaproblemen som vi hittar i databaserna centreras runt om fragmenteringen av tabellernas data. En situation som kan vara jämförbar med tabellfragmentering kan vara ett register i slutet på en stor bok. Ett enda hänvisning i registret kan peka mot flera olika sidor som finns utspridda i boken. Du måste sedan söka på varje sida efter just den information du vill ha ut. Detta är stor skillnad mot t.ex. en telefonkatalog där all information visas i en sorterad ordning. Letar man efter "Svensson" så kanske man får leta igenom flera sidor, men det är alltid i en viss sorterad ordning.
I en databas så lagrar vi till en början data på ett sätt som mer liknar telefonkatalogen, men med tiden slutar det med att det mer liknar en historiebok. Därför måste vi ibland städa upp bland alla data i ett försök att återskapa en telefonkatalogs ordning. Här nedanför kan du se en grafisk presentation av hur SQL Servern lägger upp alla data, så att vi kan diskutera ämnet i klartext.
En kort diskussion om SQL Serverns interna funktioner
Vi är mest familjära med dataposten. Poststorleken kan endast sättas genom definitionen av tabellen som håller posten (ex, en tabell med adresser kräver mer data per post än en tabell med klassificeringsnamn). I SQL Server så kan en tabell definiera en post som lagrar allt från så lite som 4 bytes till så mycket som 8 060 bytes. Den här begräsningen avgörs av storleken på datasidan, vilken kan lagra upp till 8 192 bytes (8kb). De återstående 132 byten används av SQL Server till att söka upp annan information under samlingarna. Trots att SQL Server är byggt runt 8kb sidor så är 64kb den minsta mängden data som SQL Server kan allokera. Det kallas för en bindning.För att lagra data i en sorterad ordning, som i en telefonkatalog, så använder SQL Server det som kallas ett Clustrat Index. När en vanlig databas skapas så finns det Clustrade Index på nästan alla tabeller. Men bara för att alla data finns i sorterad ordning inom sidan, så betyder inte det att de finns sorterade inom en bindning. Anledningen till det här kan härledas från en situation, där det inte finns mer plats på en sida för att kunna lägga till en post. SQL Server tar då bort cirka hälften av sidan och flyttar den till en annan sida, vilket kallas för ”splittrade sidor”. (Splittrade sidor uppstår inte med Clustrade Index på IDENTITY baserade kolumner, men hotspotting kan göra det). I vissa fall så kan den även flytta dessa data till en helt annan bindning, och den kan också allokera en helt ny bindning för att kunna göra det. Så medan vi börjar med namn som börjar på A och slutar på H på en sida, och namn som börjar på I och slutar på Z på nästa sida, så kan vi genom användningen se att namnen från A till C är lokaliserade på en sida i en bindning, D till E på en annan bindning, och S till Z tillbaka till den femte sidan i den första bindningen, osv. Det är på grund av splittrade sidor som vi ibland föredrar tabeller utan några Clustrade Index över huvud taget. Men dessa tabeller är oftast utomstående tabeller som är mycket explosiva, och i dessa situationer så föredrar vi de snabbare skrivningarna till bekostnaden av långsammare läsningar.
Anropa DBCC SHOWCONTIG
Anslut med hjälp av Query Analyzer till den databas som du vill se över. Efter det så måste du ta reda på objektID av den tabellen (eller tabellerna) som du vill granska. Jag har förenklat den här uppgiften att få ut de TOP 10 tabellerna storleksmässigt, genom att använda mig av följande script.
SELECT TOP 10
'DBCC SHOWCONTIG(' + CAST(id AS NVARCHAR(20)) + ')'
+ CHAR(10) +
'PRINT '' ''' + CHAR(10)
FROM
Sysindexes
WHERE
indid = 1 or
indid = 0
ORDER BY rows DESC
Om du exekverar detta scipt från den databas som du vill granska så får du ut en output som liknar följande (10 gånger, en för och en av de största tabellerna):
DBCC SHOWCONTIG(123456789)
PRINT ''
Sen klipper ut den och klistrat in hela resultatet i Queryfönstret och exekverar den.
Förklaring av resultaten
Resultatet av föregående kommando kommer att se ut ungefär så här:DBCC SHOWCONTIG scanning 'MyTable1' table...
Table: 'MyTable1' (1556968673); index ID: 1, database ID: 16
TABLE level scan performed.
Pages Scanned................................: 18986
- Extents Scanned..............................: 2443
- Extent Switches..............................: 9238
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 25.70% [2374:9239]
- Logical Scan Fragmentation ..................: 44.58%
- Extent Scan Fragmentation ...................: 87.07%
- Avg. Bytes Free per Page.....................: 1658.7
- Avg. Page Density (full).....................: 79.51%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
DBCC SHOWCONTIG scanning 'MyTable2' table...
Table: 'MyTable2' (183984032); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 28980
- Extents Scanned..............................: 3687
- Extent Switches..............................: 22565
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 16.06% [3623:22566]
- Logical Scan Fragmentation ..................: 83.05%
- Extent Scan Fragmentation ...................: 87.44%
- Avg. Bytes Free per Page.....................: 3151.1
- Avg. Page Density (full).....................: 61.07%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I den första tabellen, MyTable1, så kan vi se att det granskades 18 986 sidor för att skapa rapporten. Dessa sidor existerade inom 2 443 bindningar, vilket indikerar på att tabellen konsumerade cirka 97 % (i genomsnitt 7,8 sidor per bindning) av de bindningar som är allokerade till tabellen. Vi kan vidare se att medan den granskade sidorna för fragmentering så var Servern tvungen att byta bindningslokalisering 9 238 gånger. Scandensiteten bekräftar detta genom att indikera att den procentuella delen av alla sidor inom alla bindningar var kontinuerliga. I den ideala miljön så bör den visade densiteten ligga i närheten av 100. Den logiska scanfragmenteringen och bindningens scanfragmentering är indikeringar på hur väl Indexen är lagrade inom systemet där det finns ett Clustrat Index (och kan ignoreras för tabeller som inte har något Clustrat Index). I båda fallen så föredrar man en siffra nära 0. Det visas en annan avvikelse här som är lite svår att förklara, men det är det att SQL Server låter flera tabeller existera inom en enda bindning, vilket vidare förklarar detta 7,8 sidor per bindning. (Däremot kan inte flera tabeller existera inom en och samma sida).
Följande information diskuterar en lite tråkigare – men ack så viktig fråga angående konsumtionen av sidor, och vi kommer återigen att använda oss utav den första tabellen i vårt exempel. Där har vi ett genomsnitt på 1 659 lediga bytes per sida, eller att varje sida är konsumerad till 79,51 %. Ju närmare den siffran är 100, desto snabbare klarar databasen av att läsa in records eftersom det finns flera records på en enda sida. Men det här måste balanseras med vad det kostar att skriva till tabellen. Eftersom splittrade sidor uppstår då det krävs en skrivning mot en sida som är full, så kan belastningen bli enorm. Det här är dock överdrivet om du använder ett RAID 5 disksystem, eftersom RAID 5 har en mycket långsammare skrivtid jämfört med dess lästid. Med tanke på det här så kan vi tala om för SQL Server att endast fylla sidorna till en viss procentuell del.
DBCC REINDEX är ett länkat verktyg som omorganiserar din databasinformation på ett liknande sätt som Norton Defrag arbetar på din hårddisk (Se Books Online för mer information om hur du använder DBCC REINDEX). Följande rapport visar skillnaderna i data efter att vi har defragmenterat dem med hjälp av DBCC REINDEX.
DBCC SHOWCONTIG scanning 'MyTable1' table...
Table: 'MyTable1' (1556968673); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 15492
- Extents Scanned..............................: 1945
- Extent Switches..............................: 2363
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 81.94% [1937:2364]
- Logical Scan Fragmentation ..................: 15.43%
- Extent Scan Fragmentation ...................: 20.15%
- Avg. Bytes Free per Page.....................: 159.8
- Avg. Page Density (full).....................: 98.03%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
DBCC SHOWCONTIG scanning 'MyTable2' table...
Table: 'MyTable2' (183984032); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 35270
- Extents Scanned..............................: 4415
- Extent Switches..............................: 4437
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.35% [4409:4438]
- Logical Scan Fragmentation ..................: 0.11%
- Extent Scan Fragmentation ...................: 0.66%
- Avg. Bytes Free per Page.....................: 3940.1
- Avg. Page Density (full).....................: 51.32%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
Här kan vi se många nyckelförbättringar och några exempel på hur viktigt det är att Indexera korrekt. De viktigaste värdena här för oss är hur väl vi lyckades med att höja scandensiteten. Återigen med MyTable1 tabellen som referens så kan vi se att utav de 1 945 bindningarna så skedde det bara 2 363 bindningsbyten. Notera att antalet bindningsbyten här är lägre än antalet bindningar från förut. Det här beror på den mer effektiva allokeringen av alla data, och eftersom det blev en märkbar reducering av bindningsbyten så kommer sökningar efter större mängder kontinuerlig data bli avklarade mycket snabbare.
Dessa rapporter gjordes efter att det endast har skett en liten mängd processning på systemet, men vi kan ändå redan se att det har skett en relativt hög fragmentering av alla data. Tabellen MyTable1 har redan börjat visa tecken på prestandadegradering. När en ovanligt stor mängd nya data läggs in i tabellerna så kommer dessa värden snabbt att börja likna de värden vi såg i den tidigare rapporten.
I tabellen MyTable2 så kan vi se en markant skillnad från MyTable1, vilket beror på en viss Indexoptimering som jag gjorde på den tabellen. Som jag sa tidigare så använder SQL Server de Clustrade Indexen för att kunna förstå hur alla data ska sorteras. För att förhindra splittrade sidor så låter jag SQL Server fylla varje sida till inte mer än 50 %. Det låter åtskilliga INSERTs att inträffa utan att generera splittrade sidor, vilket håller vår scandensitet hög under en längre tid. Men det tillkommer med bekostning av en reducerad mängd kontinuerliga records på varje sida, och med en fördubbling av mängden yta som konsumeras av tabellen. Det är därför som antalet scannade sidor och bindningar är så mycket högre.
0 Kommentarer