Tips för hur du bygger om SQL Server Index
Förord
Du bör regelbundet (veckovis eller månatligt) utföra en databas reorganisering på alla Index på alla tabeller som finns i din databas. Det kommer att bygga om alla Index så att data inte längre är fragmenterade. Fragmenterade data kan orsaka att SQL Server får utföra onödiga dataläsningar, vilket drar ner på SQL Serverns prestanda.
Om du gör en reorganisering på en tabell med ett Clustrat Index så kommer alla eventuella icke-Clustrade Index i samma tabell att byggas om automatiskt.
Innehåll
Tips för hur du bygger om SQL Server Index
Databas reorganiseringar kan göras genom att med SQL Server Agent schemalägga att SQLMAINT.EXE körs eller genom att köra dina egna vanliga script via SQL Server Agent (se koden nedan). Tyvärr så kan inte kommandot DBCC DBREINDEX bygga om alla Index på alla tabeller i din databas, den kan bara jobba med en tabell i taget. Men om du kör följande script så kan du lätt reindexera alla tabeller i databasen:
--Ett script som reindexerar alla tabeller i din databas automatiskt
USE DatabaseName –Skriv in namnet på den databas som du vill reindexera
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT "Reindexing " + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Scriptet kommer automatiskt att reindexera alla Index på alla tabeller i den databas du väljer, och den kommer även att ge en fyllfaktor på 90 %. Du kan byta ut siffran ’90’ i koden ovan mot vilken siffra du vill för fyllfaktorn.
Om DBCC DBREINDEX används för att bygga om Index så måste man komma ihåg att medan Indexen i tabellen byggs om så blir tabellen otillgänglig för att användas av dina användare. Om t ex ett icke-Clustrat Index byggs om så sätts en gemensam tabellspärr på tabellen, vilket hindrar att alla operationer utom SELECT kan köras mot den. När istället ett Clustrat Index byggs om så sätts en exklusiv tabellspärr på tabellen, vilket hindrar all användaraccess mot tabellen. På grund av det så bör du endast köra det här kommandot då användarna inte behöver accessa tabellen som reorganiseras. [SQL Server 7.0, 2000] Uppdaterad 00-12-19.
*****
När du skapar eller bygger om ett Index, så får du ange en fyllfaktor för Indexet. Fyllfaktorn refererar till hur mycket datasidorna i Indexen fylls upp då Indexen skapas. En fyllfaktor på 100 innebär att varje datasida fylls till 100 %, och en fyllfaktor på 50 % innebär att varje datasida fylls till 50 %. Om du skapar ett Clustrat Index där fyllfaktorn är 100, så innebär det att varje gång som ett nytt record läggs in (eller kanske uppdateras) så kommer det att uppstå splittrade sidor – eftersom de nya data inte får plats på de existerande sidorna. Och flertalet splittrade sidor kan dra ner SQL Serverns prestanda.
Här följer ett exempel. Anta att du precis har lagt in ett nytt Index med standardfyllfaktorn i en tabell. När sedan SQL Server skapar det Indexet så placerar den Indexet på kontinuerliga fysiska sidor, vilket ger optimal I/O access eftersom alla data kan läsas i sekvens. Men allteftersom tabellen växer och förändras med INSERTs, UPDATEs och DELETEs, så kommer det att uppstå splittrade sidor. När sidor splittras så tvingas SQL Server att allokera nya sidor någon annanstans på hårddisken, vilket innebär att dessa nya sidor inte blir kontinuerliga med de fysiska originalsidorna. På grund av det så måste man använda random I/O access istället för sekventiell I/O access för att kunna komma åt Indexsidorna, och det går mycket långsammare.
Så vilken är den ideala fyllfaktorn? Det beror på vilken kvot av läsningar/skrivningar som applikation utför mot din SQL Server databas. En tumregel är att följa följande riktlinjer:
- Tabeller med få uppdateringar (med kvoten 100-1 för läsning mot skrivning): 100 % fyllfaktor
- Tabeller med många uppdateringar (där skrivningarna överskrider läsningarna): 50-70 % fyllfaktor
- Allt däremellan: 80-90 % fyllfaktor
Du kan komma att behöva experimentera en del för att få reda på den optimala fyllfaktorn för just din applikation. Förutsätt inte att en låg fyllfaktor alltid är bättre än en hög fyllfaktor. Medan du med en låg fyllfaktor kan reducera antalet splittrade sidor, så kan det istället öka antalet sidor som SQL Server måste läsa då en SQL-sats körs, vilket kan dra ner på prestandan. Och det är inte bara arbetslasten för I/O som ökar vid en för låg fyllfaktor, det kan också påverka din Buffer Cache. När datasidor flyttas från hårddisken till Buffern så flyttas hela sidorna till Buffern (inkluderat de tomma lagringsutrymmena). Så, ju lägre fyllfaktor du har, desto fler sidor måste flyttas till SQL Serverns Buffer. Det innebär att det finns mindre utrymme för andra viktiga datasidor att lagras på samtidigt, och det skadar prestandan.
Om du inte specificerar någon fyllfaktor så är standardfyllfaktorn 0, vilket innebär samma sak som en fyllfaktor på 100 (Indexets lövsidor fylls till 100 %, medan det finns lite plats kvar på de mellanliggande Indexsidorna). I de flesta fall så är standardvärdet inget bra val, speciellt inte för Clustrade Index. [SQL Server 6.5, 7.0, 2000] Uppdaterad 02-02-08.
*****
Om du har en tabell som har ett Clustrat Index på en monotont ökande eller sjunkande primärnyckel, och om tabellen inte utsätts för några UPDATEs eller som inte har några VARCHAR kolumner, då är den ideala fyllfaktorn för den här tabellen 100. Det beror på att en tabell av den här typen normalt sett inte kommer att utsättas för några splittrade sidor, och det medför att det inte finns någon mening i att lämna utrymme i Indexet för splittrade sidor. Och eftersom fyllfaktorn är 100 så kräver SQL Servern färre I/Os för att kunna läsa alla data i tabellen, vilket höjer prestandan. [SQL Server 7.0, 2000] Inlagd 02-0-08.
*****
Om du inte är säker på vilken fyllfaktor du ska sätta på dina Index så är ditt första steg att avgöra kvoten mellan diskskrivningar mot diskläsningar. Du gör det genom att se över dessa två Counters: ’Physical Disk Object: % Disk Read Time’ och ’Physical Disk Object: % Write Time’. Om du kör dessa två Counters mot en array så bör du kunna få en ganska bra känsla över hur många procent av din I/O som är skrivningar och läsningar. Du bör köra det här under en period som representerar en typisk arbetslast på din Server. Om den procentuella mängden skrivningar dramatiskt överskrider den procentuella mängden läsningar, så krävs en lägre fyllfaktor. Om det är tvärtom; att den procentuella mängden läsningar överskrider den procentuella mängden skrivningar, så krävs en högre fyllfaktor.
En annan Counter i Performance Monitor som du kan använda för att hjälpa dig avgöra den ideala fyllfaktorn i just din miljö är SQL Server Access Methods: Pages Splits/sec. Den här Countern mäter det antalet splittrade sidor per sekund, som uppstår i din SQL Server. För bästa prestanda så bör den här Countern hållas så låg som möjligt, eftersom splittrade sidor medför extra arbetslast från Servern och skadar prestandan. Om den här siffran är relativt hög så bör du sänka fyllfaktorn, för att förhindra nya splittrade sidor. Om den här Countern är väldigt låg så är din aktuella fyllfaktor OK, eller kanske lite för låg. Men det kan du inte veta förrän du höjer fyllfaktorn och kollar resultaten igen.
Idealet är att du vill ha en fyllfaktor som förhindrar överflödiga splittrade sidor, men som inte är så låg så att storleken på databasen ökar. Det skulle kunna leda till reducerad läsprestanda på grund av alla de extra datasidor som måste läsas.
När du väl vet kvoten mellan diskskrivningar mot diskläsningar så har du nu all information du behöver för att kunna bestämma den optimala fyllfaktorn för dina Index. [SQL Server 6.5, 7.0, 2000] Uppdaterad 00-10-09.
*****
Om du vill kunna avgöra vilken nivå av fragmentering som dina Index har på grund av splittrade sidor, så kan du köra kommandot DBCC SHOWCONTIG. Eftersom det här kommandot kräver att du vet ID:t på både tabellen och Indexet som ska analyseras, så bör du köra följande script:
--Script för att identifiera fragmenteringen i en tabell
--Deklarera variabler
DECLARE
@ID int,
@IndexID int,
@IndexName varchar(128)
--Skriv tabellen och Indexet som ska analyseras
SELECT @IndexName = 'index_name' --Skriv in namnet på Indexet
SET @ID = OBJECT_ID('table_name') --Skriv in namnet på tabellen
--Få Indexvärdena
SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName
--Visa fragmenteringen
DBCC SHOWCONTIG (@id, @IndexID)
Medan DBCC SHOWCONTIG ger dig flera olika mätningar så är Scan Density den huvudsakliga. Den siffran bör vara så nära 100 % som möjligt. Om Scan Density är lägre än 75 % så kan du behöva reindexera tabellerna i din databas, samt öka fyllfaktorn om du tycker att den aktuella fyllfaktorn som du använder inte är den lämpliga. [SQL Server 6.5, 7.0, 2000]
*****
Här följer ett script som används för att skapa DBCC SHOWCONFIG kommandon för alla Index i en eller flera tabeller. När du har kört det här scriptet så kommer det att producera ett DBCC SHOWCONFIG uttryck för varje Index, vilka du sedan kan köra för att få reda på nivån av fragmentering av dina Index. Det här scriptet är särskilt användbart då du inte vet namnen på dina Index i dina tabeller (vilket kan vara ofta):
SELECT 'dbcc showcontig (' +
CONVERT(varchar(20),id) + ',' + -- tabell ID
CONVERT(varchar(20),indid) + ') -- ' + -- Index ID
object_name(id) + '.' + -- tabellnamn
name -- Indexnamn
FROM sysindexes
WHERE id = object_id('table_name1')
OR id = object_id('table_name2')
ORDER BY
object_name(id), indid
Det här scriptet kan köras för en eller flera tabeller. Exemplet ovan är skrivet för två tabeller (table_name1 och table_name2). Om du bara vill köra för en tabell så kan du ta bort OR klausulen. Om du vill köra för fler än två tabeller så lägger du till extra OR klausuler.
När du har kört det här scriptet så kommer resultatet att bli DBCC SHOWCONFIG uttryck för var och en av tabellerna och Indexen. Resultaten kan sedan klippas ut och sedan klistras in i Query Analyzern, och sedan köras. Den kommer sedan att producera ett DBCC SHOWCONFIG resultat för varje Index i varje tabell som du har specificerat. [SQL Server 6.5, 7.0, 2000] Inlagd 02-07-31. Bidraget av Jeff M. Belina.
*****
Om du får reda på att du ofta utsätts för många splittrade sidor och därmed måste öka fyllfaktorn för dina Index, så kan du granska SQL Server Statistics Object: Page Splits/sec med hjälp av Performance Monitor. [SQL Server 6.5, 7.0, 2000]
*****
Reindexera inte dina tabeller då din databas är i aktiv produktion, eftersom det kan låsa resurser och skapa problem för dina användare. Reindexering bör vara schemalagt och ske efter arbetstid, eller i värsta fall då databasen inte används lika mycket. [SQL Server 6.5, 7.0, 2000]
*****
Om du använder kommandot CREATE INDEX då du skapar eller bygger om ett Index, så har alternativet FILLFACTOR ett eget subalternativ som kallas PAD_INDEX. Om du inte specificerar PAD_INDEX alternativet så kommer FILLFACTOR endast att tillämpas på lövsidorna i Indexet, och inte på de mellanliggande Indexsidorna. Men om du specificerar PAD_INDEX tillsammans med FILLFACTOR alternativet, så kommer den procentuella FILLFACTORn även att tillämpas på de mellanliggande Indexsidorna då Indexet skapas. [SQL Server 6.5, 7.0, 2000]
*****
Om du vill bygga om ett Clustrat Index med hjälp av CREATE INDEX kommandot, och då vi förutsätter att tabellen också har icke-Clustrade Index, så får du bäst prestanda om du använder alternativet DROP_EXISTING tillsammans med CREATE INDEX kommandot. DROP_EXISTING alternativet inkluderar vissa optimeringar som förhindrar den arbetslast som skulle bli om de icke-Clustrade Indexen i tabellen bygdes om två gånger. [SQL Server 7.0, 2000]
*****
I SQL Server 2000 så introduceras ett nytt kommando som heter DBCC INDEXDEFRAG, som används för att defragmentera både Clustrade och icke-Clustrade Index i en tabell eller i en Indexerad vy. Den gör det genom att defragmentera och komprimera lövnivån av Indexet, så att den fysiska ordningen av Indexet matchar den (från-vänster-till-höger) logiska ordningen av lövnoderna, vilket höjer prestandan. Det är ofta att föredra att använda DBCC INDEXDEFRAG kommandot istället för DBCC DBREINDEX kommandot, eftersom det första kommandot inte håller låsningar under en längre period såsom DBCC DBREINDEX gör. Det innebär att den skulle kunna köras under produktionstid utan att nämnvärt påverka prestandan, trots att sådana här uppgifter idealiskt bör köras medan databasen inte är så sysselsatt. [SQL Server 2000] Inlagd 00-10-19.
*****
Ett sätt att snabba på reindexering av dina databaser är genom att försäkra dig om att din SQL Server databas och loggfilerna är defragmenterade innan du reindexerar din databas. Genom att försäkra dig om att databasen och loggfilerna är kontinuerliga (defragmenterade) så kommer inte bara reindexeringen att gå snabbare, utan det kräver också färre I/O resurser – vilket höjer SQL Serverns prestanda. Om du har SQL Server 2000 så finns det ett defragmenteringsverktyg tillgängligt för ett sådant här syfte. Om inte, så kan du använda dig av ett tredje parten defragmenteringsverktyg. [SQL Server 6.5, 7.0, 2000] Uppdaterad 02-04-11.
0 Kommentarer