Tips för hur du använder SQL Server Index Tuning Wizard
Förord
Din Index Tuning Wizard är ett kraftfullt verktyg som är designat till att hjälpa dig identifiera existerande Index som inte används, samtidigt som den ger dig rekommendationer om nya Index som du kan använda för att snabba på dina SQL-satser. Den använder sig utav de aktuella SQL-satserna som körs mot din databas, så dess rekommendationer baseras på hur din databas faktiskt används. De SQL-satser som behövs för analysen kommer från de SQL Server Profiler Traces som du skapar. [SQL Server 7.0, 2000]
Innehåll
Tips för hur du använder SQL Server Index Tuning Wizard
Använd dig utav SQL Server Index Wizard tillsammans med en lämplig Tracefil för att hjälpa dig identifiera potentiella Indexerade vyer (för det krävs SQL Server 2000 Enterprise). När din Index Wizard kör så letar den automatiskt efter potentiella Indexerade vyer, och den rekommenderar de som den finner. Men lita inte enbart på detta verktyg när det gäller att identifiera Indexerade vyer, eftersom den inte kan identifiera alla möjliga kandidater. [SQL Server 2000] Inlagd 02-06-19.
*****
Om du inte behöver den fulla kraften av det som SQL Server Index Wizard kan ge dig, men om du fortfarande vill ha hjälp då du skapar Index för en tabell, så kan du använda dig av alternativet ”Perform Index Analysis” under Query Analyzerns ”Query” meny i SQL Server 7.0. Om du har SQL Server 2000 så väljer du ”Index Tuning Wizard” under Query Analyzerns ”Query” meny.
Istället för att använda sig av en Profiler Tracefil för att utföra sin analys, så använder den sig av den SQL-satsen som finns i Query fönstret. Trots att det inte blir en lika grundlig analys som när du använder Index Tuning Wizard, så är det en bra början då du vill analysera prestandan av specifika SQL-satser i din databas. [SQL Server 7.0, 2000] Inlagd 00-12-29.
*****
När du skapar den Profiler Tracefil som Index Tuning Wizard ska basera sin Indexanalys på så bör du välja att göra den under en tid på dagen som är representativ för de typiska transaktioner som körs i din SQL Server applikation. Eftersom din Index Tuning Wizard baserar sina rekommendationer på aktuella SQL-satser så bör SQL-satserna i din Tracefil vara representativa för hur dina användare använder applikationen. [SQL Server 7.0, 2000]
*****
Ju längre du kör din Profiler Trace, desto mer korrekta blir din Index Tuning Wizards rekommendationer. Det beror på att du fångar in flera olika typiska SQL-satser som körs i din applikation. Men kom ihåg att antalet SQL-satser som Index Tuning Wizard kan analysera samtidigt är begränsat till 32 767 satser, och ju längre Tracefilen blir, desto längre tid tar det för analysen att bli klar (kan ta flera timmar). [SQL Server 7.0, 2000]
*****
Om du finner att din Index Tuning Wizard tar längre tid på sig att bli klar än vad du egentligen har tid med, så bör du överväga att ändra standardvärdet i ”Maximum columns per Index” under Avancerade Inställningar, från 16 till ett mindre värde. Det reducerar antalet möjliga Index som Index Tuning Wizard behöver granska, vilket sparar lite tid. I de flesta fall (speciellt för OLTP applikationer) så bör du inte ha Index med flera kolumner. [SQL Server 7.0, 2000] Inlagd 00-09-08.
*****
Fånga inte in mer än vad du behöver i din Profiler Tracefil. Samla t ex bara ihop data för en enda databas, och inte för alla databaser på din Server. Samla alltså inte in flera händelser (events) eller datakolumner än vad du behöver. De enda händelser och datakolumner som din Index Wizard kräver inkluderar händelserna SQL:BatchCompleted och RPC:Completed i TSQL kategorin, tillsammans med EventClass- och Textdatakolumnerna. Ju färre händelser och datakolumner som du fångar in i din Profiler Tracefil, desto mindre arbetslast kräver Profilern av din Server för att fånga in alla data. [SQL Server 7.0, 2000] Inlagd 00-09-01.
*****
När du skapar en Trace för din Index Tuning Wizard så bör du överväga att använda ”Create Trace Wizard” för att skapa en ”Find the worst performing Queries”-Trace. Du kan ställa in den här Tracen att bara fånga in sådana SQL-satser som kör längre än en specifik lång tid, som t ex 1 000 millisekunder. Generellt sett så brukar jag ställa in den på 5 millisekunder och sedan låta den köra hela dagen (i perioder), för att därefter mata min Index Tuning Wizard med denna Trace.
Mitt mål här är förstås att begränsa antalet SQL-satser som ska optimeras till de SQL-satser som presterar sämst. När du gör det så bör du försäkra dig om att din Index Tuning Wizard inte söker sådana Index som eventuellt kan tas bort. Det beror på att du inte ska samla ihop prestandadata om alla SQL-satser, bara från de som går långsamt. Om du söker efter Index som kan tas bort så kan det te sig så att du tar bort ett Index som faktiskt behövs, eftersom SQL-satserna som använder sig av det kör snabbare än den tiden som är specificerad ovan. [SQL Server 7.0] Uppdaterad 00-09-21.
*****
Kör inte SQL Server Profiler eller Index Tuning Wizard på din produktions SQL Server. Båda verktygen använder sig av SQL Server resurser som är bäst lämpade åt användarna. Det idealiska vore att köra dem från en arbetsstation som är ansluten till Servern via nätverket. En gång körde jag Index Wizard mot en databas med över 800 tabeller. Medan Index Wizard körde så använde den över 1 GB av virtuellt minne, vilket tyngde min dator enormt. Som tur var så utförde jag analysen på en desktop istället för min SQL Server. Om jag hade kört samma analys på min produktionsserver så hade mina användare klagat högljutt. [SQL Server 7.0, 2000] Uppdaterad 00-09-21.
*****
Även om du kör Index Tuning Wizard från en annan dator än den databas som du analyserar ligger på, så kommer ändå Index Tuning Wizard att tynga ner produktionsservern medan den kör. På grund av det så bör du endast köra Index Tuning Wizard medan produktionsservern inte är lika sysselsatt. Ett annat alternativ är att lagra om produktionen till en icke-produktionsserver, och sedan köra Index Tuning Wizard mot backup-databasen på icke-produktionsservern. [SQL Server 7.0, 2000] Inlagd 00-09-01.
*****
När du väl har optimerat dina Index med hjälp av Index Tuning Wizard så ska du inte räkna med att du inte behöver göra detta igen. SQL-satser ändras ofta med tiden, och du bör regelbundet köra Index Tuning Wizard igen för att se om den rekommenderar några nya ändringar, baserat på de olika SQL-satser som har ändrats med tiden. [SQL Server 7.0, 2000]
*****
Bli inte blind och acceptera alla de rekommendationer som Index Tuning Wizard ger dig. Titta personligen igenom varje rekommendation, för att sen – baserat på dina kunskaper om databasen och hur den används – acceptera eller inte acceptera rekommendationerna baserat på rekommendation-för-rekommendation. Index Tuning Wizard kan t ex rekommenderar dig att lägga till ett nytt Index på en tabell som du vet utsätts för enorma mängder INSERTs och UPDATEs. Att lägga till ett Index på en sådan tabell kan vara en bra idé, eller så är det inte en bra idé.
Alltså, innan du blint accepterar de rekommendationer som Index Tuning Wizard gör, så bör du kolla igenom de SQL-satser som körs mot den tabellen som Index Tuning Wizard har rekommenderat att sätta ett Index på, för att se om det kanske är SQL-satserna själva som det är fel på. Istället för att du behöver ett nytt Index så kanske du måste skriva om en eller flera SQL-satser.
Index Tuning Wizard kan också rekommendera att du ska ta bort ett eller flera Index. Granska alltid dessa rekommendationer noggrant innan du väljer att ta bort ett Index. Kom ihåg att Index Tuning Wizard gör sina rekommendationer baserat på de Tracedata som du har gett den. Det är väldigt möjligt att den Tracen som den använder inte inkluderar alla relevanta data. Låt säga att du t ex kör långa rapporteringar på nätterna, som kräver vissa Index, och att denna information inte fångades in i den Tracen som du skapade. Om du skulle ta bort något Index som behövs av dessa rapporteringar så skulle dessa rapporter ta för evigt att köra, eftersom de saknar de Indexen som behövs.
Vidare så ska du inte lita på att Index Tuning Wizard rekommenderar alla Index i dina tabeller. Du bör basera dina originalval av dina Index utifrån de olika SQL-satser som du förväntar dig ska köras mot dina data. Du bör endast använda Index Tuning Wizard som ett extraverktyg till ditt egentliga arbete, för att hjälpa dig finjustera det. [SQL Server 7.0, 2000] Uppdaterad 00-12-29.
*****
Ibland kan det vara så att Index Tuning Wizard inte rekommenderar ett Index trots att du vet att det kan behövas ett. Det kan ske då SQL-satserna är komplicerade, eller om de är en del av en större lagrad procedur. Om du skulle stöta på en sådan situation så bör du överväga att bryta upp den komplicerade SQL-satsen eller den lagrade proceduren till mindre SQL-satser, och sedan köra dessa individuellt genom Index Tuning Wizard. [SQL Server 7.0, 2000] Inlagd 00-09-01.
*****
Om du har kört Index Tuning Wizard förut så kanske du vet att den kan ta över en hel del resurser medan den kör, och att det dessutom kan ta flera timmar att analysera dina data – beroende på hur stor din Tracefil är. I SQL Server 2000 så kan du starta och konfigurera Index Tuning Wizard från kommandoprompten, genom att använda dig av verktyget ’itwiz’. Fördelen med det är att du kan schemalägga Index Tuning Wizard till att köra medan din SQL Server inte är så sysselsatt. Trots att du kan välja vilket schemaläggningsverktyg som helst så kommer SQL Server Agent att försöka förhindra det när du vill köra det. Se SQL Server Books Online för mer detaljerad information. [SQL Server 2000] Inlagd 01-03-06.
*****
Medan Index Tuning Wizard kör så skapar den det vi kallar hypotetiska Index i tabellen ’sysindexes’. Namnen på dessa Index börjar med ’hind_%’. Dessa tabeller används av Index Tuning Wizard för att kunna avgöra om det behövs några nya Index i dina tabeller.
Normalt sett så bör dessa hypotetiska tabeller raderas när Index Wizard är klar, men om Index Wizard avbryts innan den har hunnit avsluta så kan det hända att den lämnar kvar dessa hypotetiska Index i ’sysindexes’ tabellen.
I vissa fall så kan existerandet av dessa tabeller leda till ovanliga prestandaproblem. Det som kan hända är att vissa lagrade procedurer tvingas kompileras om varje gång de körs, fastän de inte ska det.
Det bästa sättet att försäkra sig om att du inte har några onödiga ’hind_%’ tabeller i din ’sysindexes’ tabell är genom att köra ett
Det här problemet startade i SQL Server 7.0 SP2, och finns fortfarande kvar i SQL Server 2000. [SQL Server 7.0, 2000] Inlagd 01-10-15.
*****
När du kör Index Tuning Wizard så har du möjligheten att välja om du vill ha en fast (snabb), medium (mellan) eller thorough (grundlig) analys (i SQL Server 7.0 kan du bara välja snabb eller grundlig). När du analyserar stora Profiler Traces så har ditt val en betydlig påverkan på hur snabbt analysen blir klar. Men försök inte att ta några genvägar här, du bör alltid välja en grundlig analys. Du vill ju trots allt ha de mest optimala Indexen i din databas, så varför skulle du vilja göra en mindre grundlig analys och därmed få en mindre optimal samling Index i din databas? [SQL Server 7.0, 2000] Inlagd 02-06-27.
*****
I de flesta fall så kan det bästa sättet att utföra en Indexanalys på, genom att använda GUI gränssnittet till Index Tuning Wizard. Men om du kör väldigt många Index Tuning Wizard analyser så bör du automatisera den här uppgiften genom att använda ’itwiz’ kommandots ’line’ verktyg. Du kan använda det här verktyget tillsammans med de lämpliga kommandolinealternativen för att avsluta någon analys, precis som med GUI gränssnittet.
Du kan dessutom använda SQL Server Agent Scheduling för att schemalägga några analyseringsjobb till att köras på bekvämare tider, då inte användarna behöver påverkas nämnvärt. Se SQL Server Books Online för att lära dig hur man använder det här kommandot. [SQL Server 7.0, 2000] Inlagd 02-08-13.
0 Kommentarer