Utför en prestandaanalys av din SQL Server #7
Förord
Analysering av Index är ingen lätt uppgift, men kritisk för din Servers prestanda. När det gäller analysering av användandet av Indexen i SQL Server databaser så kan jag ibland bli lite överväldigad. Till exempel; Hur ska jag gå tillväga när jag ska analysera Index i en databas med över 1 500 tabeller? Att analysera ett enda Index är ganska rakt på, men att analysera flera tusen Index i flera databaser är ingen lätt uppgift. Vare sig uppgiften är lätt eller inte så är den viktig om du vill optimera dina SQL Server databasers prestandaInnehåll
»»
»
»
»
»
»
»
»
»
»
»
Relaterade artiklar
» Alla SQL Server Index skapas inte lika» Prestandaoptimera SQL Server JOINs
» Tips för hur du använder SQL Server Index Tuning Wizard
» Tips för hur du bygger om SQL Server Index
» Tips på hur du optimerar icke-Clustrade SQL Server Index
» Tips på hur du optimerar SQL Serverns Clustrade Index
» Tips på hur du optimerar SQL Serverns Index
» Tips på hur du optimerar SQL Serverns kombinerade Index
» Tips på hur du optimerar täckande SQL Server Index
Checklista för prestandan av SQL Serverns databasindex
av Brad M. MacGee
Checklista för prestandaanalys av Index
Skriv in dina resultat i tabellen ovan
När du ska analysera ett stort antal Index så finns det två olika sätt att gå tillväga. Ett alternativ är att bryta ner kärnan till mindre, och mer hanterbara bitar, och då fokuserar du dig i första hand på de Index som mest kommer att påverka din totala SQL Server prestanda. Du kan t ex påbörja din analys på de mest sysselsatta databaserna i din Server. Om sedan databasen har många tabeller så kan du börja med de tabeller som innehåller mest data, och sedan arbeta dig ner till de med lite mindre data. På så sätt så kan du fokusera dina påbörjande insatser på de areor där de högst troligt kommer att ge den största positiva påverkan på Serverns prestanda.
Det andra alternativet (det som jag oftast följer eftersom jag är lite lat) är att använda dig av ”hanterbarhet genom uteslutningsmetoden”. Vad jag menar med det är att om du inte ser några direkta prestandaproblem i en databas så finns det ingen anledning att undersöka varje Index i den databasen. Men om databasen skulle visa på prestandaproblem så kan det finnas en chans att Indexen är mindre optimala, och då bör du hålla extra koll på dem – speciellt om databaserna är missionskritisk. Och om det finns väldigt många Index att analysera så skulle jag fokusera på att börja med de största, eftersom det högst troligt är de som orsakar prestandaproblemen. I fall med t ex 1 500 tabeller så skulle jag bara analysera ett dussintal noggrant (de väldigt stora tabellerna), eftersom det skulle vara dem som jag kände behövde mest uppmärksamhet.
Hur du än väljer att analysera Indexen i den databas som du hanterar så måste du skapa en vettig plan, och sedan följa den systematiskt.
Som du kanske redan har märkt så är inte Checklistan längst upp på sidan särskilt lång. Det är så avsiktligen. Kom ihåg att målet med den här artikelserien är att identifiera de ”simpla” och ”uppenbara” prestandaproblemen, och inte att finna alla problemen. De som jag har listat ovan kommer att ta dig en lång väg igenom att identifiera och korrigera de simpla Indexrelaterade prestandaproblemen. När du väl har fått dessa ur vägen så kan du börja fokusera din tid på de svårare problemen. Du kan t ex ta dig en titt på följande artiklar för Indexrelaterade tips – vissa är mer avancerade (se längst upp på sidan för de relaterade artiklarna):
- Index (generella)
- Index (Clustrade)
- Index (Kombinerade)
- Index (Täckande)
- Index (Icke-Clustrade)
- Index (Bygga om)
- Index Tuning Wizard
Om du inte redan har gjort det så bör du kolla igenom var och en av dessa tipssidor.
Har du nyligen kört Index Tuning Wizard?
Ett av de bästa verktygen som Microsoft har gett oss i SQL Server 7.0 och 2000 är Index Tuning Wizard. Det är inte ett fulländat verktyg, men det kan hjälpa dig att identifiera sådana existerande Index som inte används tillsammans med rekommendationer på nya Index som kan användas för att höja hastigheten i SQL-satser. Om du kör SQL Server 2000 så kan jag även rekommendera att du använder Indexed Views. Den använder sig av de faktiska SQL-satser som körs mot databasen, så dess rekommendationer baseras på hur databasen egentligen används. De SQL-satser som behövs för analysen kommer från de SQL Server Profile Traces som du skapar.En av de första saker jag gör när jag ska göra en analys av en ny SQL Server är att fånga in Serverns aktivitet i en sökning, en ”Trace”, och sedan köra Index Tuning Wizard mot den Tracen. I många fall så kan detta snabbt hjälpa mig identifiera sådana Index som inte används och som därmed kan raderas med en gång, samt hjälpa mig identifiera sådana nya Index som borde finnas för att kunna höja SQL Serverns prestanda.
Här följer några tips för hur du ska använda Index Tuning Wizard när du ska analysera SQL Serverns databasindex:
- När du fångar in din Profiler (vilken används av Index Tuning Wizard för att utföra sin analys) så måste du fånga in dina data under en tidpunkt på dagen som är representativ för en normal last i din databas. Jag brukar vanligtvis välja i mitten på morgonen eller i mitten på eftermiddagen, och sedan köra Profiler Trace i en timme.
- Efter att du har fångat in din Profiler Trace så kan du egentligen köra Index Tuning Wizard när som helst, men det är rekommenderat att du kör den när inte databasen är så belastad – lämpligen efter arbetstid. Det beror på att när analyseringen utförs av Index Tuning Wizard så kan det uppstå lite extra arbetslast på Servern, och det är onödigt att påverka Serverns prestanda negativt om det inte behövs. Försök också att undvika analyseringar på själva produktionsservern (din Wizard måste ändå vara ansluten till produktionsservern), så du kan genom att köra din Wizard på en annan Server reducera lasten på produktionsservern medan analysen utförs.
- Trots att det kan ta lite extra tid för analysen att avslutas så måste du under setupen av Index Tuning Wizardens gång specificera en del alternativ som försäkrar en grundlig analys. Dessa alternativ inkluderar att INTE välja ”Keep all existing Indexes”, eftersom du vill kunna identifiera sådana Index som inte används; SPECIFICERA att du vill ha en ”Thorough” (grundlig) analys, och inte en ”Fast” eller ”Medium”; Att INTE välja ”Limit the number of workload queries to sample”; Att HÅLLA KVAR det maximala standardvärdet 16 på ”Maximize columns per Index”; samt att SPECIFICERA att alla kolumner kan väljas för att optimeras. Genom att göra dessa inställningar så låter du din Index Tuning Wizard att göra sitt jobb grundligt och noggrant, trots att det kan ta flera timmar för din Wizard att bli klar (beroende på hur stor din Profiler Trace är samt hur snabb den hårdvara är som du utför analysen med).
Not. Dessa instruktioner gäller endast för SQL Server 2000. Instruktionerna för SQL Server 7.0 skiljer sig lite från de ovanstående. - När analysen väl är avslutad så kanske din Index Tuning Wizard inte har några rekommendationer att ge. Annars kan den rekommendera dig att ta bort en eller flera Index, lägga till en eller flera Index, eller kanske både och. Trots att Index Tuning Wizard är ett bra verktyg så är det som sagt inte fulländat perfekt. Innan du sätter dessa rekommendationer i verket så bör du granska dem. Din Wizard kan t ex rekommendera dig att radera ett specifikt Index, medan du vet att just det där Indexet är särskilt behövligt. Så varför har din Index rekommenderat dig att radera just det Index som du vet behövs vara kvar? Jo, det beror på att din Index inte analyserar varenda Index i din Tracefil (den analyserar bara en del av filen). Dessutom kan det vara så att dina infångade Tracefildata inte inkluderar den SQL-sats som använde sig av just det Indexet. I sådana fall så kan din Wizard rekommendera dig att radera ett Index, fast det egentligen inte är en bra idé alls. När du sedan väl har försäkrat dig om att ett Index inte behövs, då kan du radera det.
Om din Wizard rekommenderar dig att lägga till nya Index så bör du först granska dem, och sen även jämföra dem med de redan existerande Indexen i tabellen för att se om de verkar vettiga, eller om de bara kommer att skapa nya problem. Ett rekommenderat Index kan t ex hjälpa en särskild SQL-sats, men den kan också göra en vanlig INSERT operation som körs tusen gånger i timmen långsam. Din Wizard kan inte veta det här så du måste göra ditt val; antingen kör vissa SQL-satser lite snabbare medan dina INSERT operationer kör lite långsammare, eller tvärtom.
Slutligen; om inte din Index Tuning Wizard rekommenderar några nya Index så betyder inte det att nya Index inte behövs. Det beror bara på att enligt de data som analyserades från din Tracefil så fann den inga nya Index att rekommendera. Du bör överväga att köra flera Traces över flera dagar för att få en bredare samling data, för att bättre kunna identifiera nödvändiga Index. Men inte ens då kan din Index Tuning Wizard finna alla de nödvändiga Indexen, men den kan finna de som uppenbarligen kan behövas.
När du väl har utfört din analys och gjort alla rekommenderade ändringar, så rekommenderar jag att du gör ytterligare en analys för att se vilken effekt dina ändringar utgjorde. Kom också ihåg att ditt användande av Index Tuning Wizard inte är en engångsföreteelse. De underliggande data i din databas kan komma att ändras allteftersom tillsammans med de olika SQL-satser som körs. Så du bör köra dina Tracer och analyser regelbundet på din Server för att hålla en jämn gång.
Har alla tabeller i varje databas ett Clustrat Index?
Som en tumregel så bör varje tabell i varje databas ha ett Clustrat Index. Generellt sett (men inte alltid) så bör det Clustrade Indexet finns på en kolumn som ökar monotont, såsom identitetskolumner eller andra kolumner där värdet ökar (och är unikt). I många fall så är primärnyckeln en ideal kolumn för ett Clustrat Index. Om du har någon erfarenhet med att optimera prestandan i SQL Server 6.5 så kanske du har hört att det inte är någon bra idé att sätta Clustrade Index på kolumner där värdena ökar monotont, eftersom det kan skapa en så kallad ”hotspot” på hårddisken och därmed skapa vissa prestandaproblem. Det är sant i SQL Server 6.5.
I SQL Server 7.0 och 2000 så orsakar dessa ”hotspots” generellt sett inte några problem. För att en ”hotspot” ska kunna ha en negativ påverkan på prestanda i SQL Server 7.0 och 2000 så måste du ha över 1 000 transaktioner per sekund. Faktum är att du under dessa omständigheter kan dra fördel av ”hotspots” eftersom de eliminerar splittrade sidor.
Det beror på följande; Om du lägger in en ny post i en tabell som har ett Clustrat Index som primärnyckel, och då denna nyckel ökar monotont, så innebär det att varje INSERT fysiskt sett kommer att hamna en efter en på hårddisken. Tack vare det så kommer det inte att ske några splittrade sidor, vilket i sig kommer att spara arbetslast. Det här beror på att SQL Server har förmågan att avgöra om de data som läggs in i en tabell har en monoton ökningssekvens, och då kommer det inte att bli några splittrade sidor då detta sker.
Om du lägger in flera poster på en gång (en tabell utan något Clustrat Index) så kommer inte posterna att läggas i någon specifik ordning på datasidorna, oavsett om dina data ökar monotont eller inte. Det resulterar i att din SQL Server måste arbeta hårdare (mer läsning) för att kunna accessa data som söks från hårddisken. Om å andra sidan ett Clustrat Index läggs in i tabellen så kommer alla data att lagras sekventiellt på datasidorna, och generellt sett så krävs det mindre I/O att hämta data när man söker det från hårddisken.
Om data läggs in i ett Clustrat Index i mer eller mindre blandad ordning så läggs det också in blandat på datasidorna, vilket liknar problemet med att lägga in mycket data på en gång – det bidrar till splittrade sidor.
Så än en gång så är den bästa rekommendationen att lägga in ett Clustrat Index på en kolumn vars värden ökar monotont (förutsatt att det finns en kolumn som gör det) för att få bäst prestanda. Det gäller speciellt om en tabell utsätts för många INSERTs, UPDATEs eller DELETEs. Men om en tabell inte utsätts för så många datamodifikationer utan istället utsätts för en hel del SELECTs, så är den här rekommendationen mindre användbar och du bör istället överväga andra alternativ vid Clustrade Index.
Som en del i din Indexanalys så bör du se om varje tabell i din databas har ett Index eller inte. Om det inte finns något Index alls så bör du allvarligt överväga att lägga till ett Clustrat Index, baserat på de tips du har fått här ovan. Virtuellt sett så finns det ingen nackdel med att lägga till ett Clustrat Index till en tabell där det inte finns något Index alls.
Är någon av kolumnerna i någon tabell Indexerade fler än en gång?
Det här kanske låter som ett självklart förslag, men det är vanligare än du kanske tror – speciellt om en databas har funnits ett tag och har administrerats av flertalet DBAs. SQL Server bryr sig inte om du gör det här, så länge som dina Index har olika namn. Så när du granskar de aktuella Indexen i din databas så bör du se efter om några tabeller har några onödiga duplikat av Index. Genom att radera sådana Index så spar du inte bara plats på hårddisken, utan du kan även höja hastigheten på dataaccesser och modifikationer i den tabellen.Ett vanligt exempel på duplicerade Index i en tabell är att glömma att kolumner med primärnycklar samt kolumner som är specificerade som unika, blir Indexerade automatiskt, och att sedan Indexera dem igen under ett annat Indexnamn.
Finns det något Index som inte används i någon SQL-sats?
Här kommer ett annat självklart förslag, men som också är ett vanligt problem – speciellt om de initierande Indexen blev ”framgissade” av DBAs och utvecklare innan databasen gick ut i produktion. Du kan inte se om något av tabellens Index används eller inte bara genom att kolla på dem, så det är inte helt lätt att identifiera oanvända Index.Ett av de bästa sätten att identifiera oanvända Index är genom att använda Index Tuning Wizard, vilket vi har diskuterats tidigare.
Onödiga Index (såsom duplikat av Index) tar inte bara upp plats på hårddisken, utan bidrar även till mindre optimala dataaccesser samt modifikationsprestanda.
Är Indexen för breda?
Ju bredare ett Index är, desto bredare kommer Indexet att bli fysiskt och desto mer arbete måste SQL Server gå igenom för att kunna accessa och modifiera data. På grund av det så bör du undvika att sätta Index på väldigt breda kolumner. Ju smalare ett Index är, desto snabbare kommer den att prestera.Kombinerade Index (Composite Index, Index som inkluderar två eller flera kolumner) bidrar dessutom till liknande problem. Om det är möjligt så bör du generellt sett helst undvika Kombinerade Index. Ofta innebär ett överdrivet användande av Kombinerade Index i en databas att databasens design inte är perfekt.
Du kan dock inte alltid undvika att Indexera breda kolumner eller användandet av Kombinerade Index. Och är det så att du måste använda det så var noga med att ha granskat ditt val noggrant och sett över att det inte finns några andra alternativ som kanske kan ge dig bättre prestanda.
Har de JOINade tabellerna korrekt Index i de JOINade kolumnerna?
Den kolumnen (eller de kolumnerna) som används som används i relaterade tabellerna bör vara Indexerade för att få bästa prestanda. Det här förslaget är ganska rakt på och relativt självklart, men att analysera dina Index för optimal JOIN prestanda är ingen lätt uppgift. Det krävs att du är medveten om alla JOINs som utförs i din databas för att kunna genomföra din analys till fullo.Vad många människor glömmer bort när de skapar relationer med primär- eller externa nycklar (vilka oftast används i JOINs) är att medan Index skapas automatiskt på kolumnen (kolumnerna) med primärnycklar, så gör det inte det på kolumnen med de externa nycklarna (foreign keys). Så om du vill ha ett Index på kolumnen med den externa nyckeln så måste du sätta dit den manuellt.
På grund av att det här ofta glöms bort så bör du i din analys identifiera alla relationer i din tabell som har primär- och/eller externa nycklar, och sedan verifiera att varje kolumn som har en externa nyckel har ett korrekt Index.
Förutom det här så kan du använda Index Tuning Wizard för att identifiera saknade JOIN Index, men jag har funnit att den inte kan identifiera saknade Index på de JOINade tabellerna. Så när allt kommer omkring så är det väldigt svårt att identifiera alla de kolumner som skulle kunna dra fördel av ett korrekt Index, så till vida att du inte vet vilka vanliga typer av JOINs som körs mot din databas.
Är Indexen unika nog för att vara användbara?
Bara för att en tabell har ett eller flera Index så innebär inte det att SQL Server Query Analyzer kommer att använda sig utav dem. Innan de används så måste din Query Optimizer ha utsett dem som användbara. Om kolumnen i en tabell inte är minst 95 % unikt så kommer din Query Optimizer högst troligen inte att använda ett tillgängligt icke-Clustrat Index baserat på den kolumnen. På grund av det här så bör du inte sätta till ett icke-Clustrat Index på en kolumn som inte är minst 95 % unikt. En kolumn med t ex ”Ja/Nej” kommer inte att bli minst 95 % unikt, och om du skapar ett Index på den kolumnen så skapar du ett Index som inte kommer att användas senare. Och det vi har lärt oss är att existerande Index som inte används drar ner på prestandan.För mer information om selektivitet så kan du kolla igenom artikeln längst uppe på sidan, ”Alla SQL Server Index skapas inte lika”.
Som en del i din analys så bör du se över data i dina tabeller. Du bör med andra ord se över de data som är lagrade i tabellerna, och sedan ta en extra titt på alla kolumner som är Indexerade. Generellt sett så kan det vara ganska uppenbart om data i en kolumn är selektivt eller inte. Om du ser att dina data är ”kvinna” eller ”man”, eller ”ja” eller ”nej”, osv, så vet du att dina data inte är selektiva, och alla Index som sätts på de kolumnerna är ett slöseri på plats och de kan hindra din prestanda.
Drar du fördel av de täckande Indexen?
Ett täckande Index, som är en form av Kombinerat Index, inkluderar alla kolumner som man har refererat till genom en SELECT-, JOIN- eller WHERE klausul i en SQL-sats. Tack vare det så kommer Indexen att innehålla alla data som du letar efter, vilket leder till att inte SQL Server måste leta upp alla faktiska data i tabellen. Det kan reducera både logisk och/eller fysisk I/O, och därmed också höja prestandan. Medan icke-täckande kombinerade Index kan dra ner på prestandan så kan täckande kombinerande Index faktiskt vara användbart, och i många fall så kan det höja en SQL-sats prestanda.Den svåra delen kan vara att klura ut vart ett täckande Index kan komma till bäst användning. Trots att Index Tuning Wizard kan hjälpa dig med det så missar den ändå flera möjligheter där ett täckande Index kan komma till användning. I övrigt så är det enda sättet att få reda på om de kommer att vara användbara genom att noggrant granska alla vanliga SQL-satser som körs mot din databas – vilket är nästan omöjligt, så till vida att du inte är väldigt, väldigt uttråkad och inte har något annat för dig.
I den här delen av din analys så är inte målet att identifiera nya täckande Index som sådana, utan att du är medveten om dem så att du kan dra fördel av dem när du stöter på ett tillfälle där de kan vara användbara.
Hur ofta återskapas dina Index?
Med tiden så kommer dina Index att bli fragmenterade och orsaka extra arbete för din SQL Server när den ska accessa dem, vilket kan skada prestandan. Den enda lösningen för det här är att regelbundet defragmentera alla Index (se artikeln ”Tips för hur du bygger om SQL Server Index” längst upp) i din databas. Det finns ett flertal olika sätt att utföra det här på. Men själva processen om hur du gör det kommer inte att diskuteras här, eftersom det finns skrivet i andra artiklar samt i SQL Server Books Online. Målet i din analys är istället att ta reda på huruvida Indexen i den databas som du analyserar defragmenteras regelbundet eller inte. Hur ofta du defragmenterar dina Index kan variera från dagligt, veckovis eller kanske till och med månatligt, och det beror på hur ofta dina data modifieras samt storleken på databasen. Om det sker många modifikationer varje dag i din databas så bör defragmenteringen ske lite oftare. Men om databasen är väldigt stor så innebär det att det kommer att ta längre tid att defragmentera. Det kan leda till att du inte kan defragmentera lika ofta eftersom defragmenteringsprocessen skulle kräva en hel del resurser och skulle därmed påverka användarna negativt. Som en del i din analys ska du ta reda på hur ofta defragmenteringen sker för tillfället, och sedan bedöma om det är den optimala frekvensen.
Och slutligen; om Indexen inte återskapas för tillfället så bör de göra det. Som en del i din analys så bör du försäkra dig om att något lämpligt återskapningsshcema för Indexen sätts i verket.
Vilken är din fyllfaktor för Indexen?
Fyllfaktorn är nära relaterat till Indexens återskapning. När du skapar ett nytt Index, eller bygger om ett Index som redan finns, 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å vilket är den ideala fyllfaktorn? Det beror på vilken kvot av läsningar och 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 jämfört med 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 ytorna). 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).
Som en del i din analyseringsprocess så måste du avgöra vilken fyllfaktor som används för tillfället när man skapar nya Index, eller om man bygger om existerande. I virtuellt alla fall (förutom vid read-only databaser) så är standardvärdet på 0 inte något lämpligt värde. Du bör istället välja en fyllfaktor som lämnar en lämplig mängd fri yta kvar, såsom vi har diskuterat här ovan.
Och nu då?
Vårt mål är att utföra den del av prestandaanalyseringen som vi har diskuterat i den här artikeln, på var och en av databaserna i din SQL Server (vilket låter som – och är – en hel del jobb), och sedan använda den informationen du får ut till att göra lämpliga korrigeringar.När du har klarat av den här delen i analysen, så är du nu redo för att ta en titt på hur Transact-SQL och din applikation kan påverka prestandan.
0 Kommentarer