Dra fördel av SQL2K Performance Tuning Tools
Förord
SQL Server 2000 har flera verktyg och tillbehör som du kan använda för att underlätta ditt arbete för dina SQL Server applikationen. Detta inkluderar:
Query Analyzer
Profiler
Index Wizard
System (Performance) Monitor
I nästkommande sektioner kommer vi ta en titt på hur du kan använda dessa verktyg för att optimera din SQL Server baserade applikation.
Innehåll
»»
»
»
»
»
»
»
»
»
»
Skribent: Brad M. McGehee
SQL Server 2000 Query Analyzer är inte bara ett utmärkt verktyg för att utveckla och debugga Transact-SQL kod, utan även för att finjustera prestanda i Transact-SQL kod. I detta avsnitt ska vi titta på vad Query Analyser kan göra och även lära oss mer om hur det kan användas för att hjälpa till att identifiera och lösa prestanda problem. Liksom för många av de mer avancerade SQL Server 2000 verktygen behöver du ha ganska god kännedom och förståelse om Transact-SQL för att få ut så mycket som möjligt av verktyget. Låt oss titta på några av nyckelfunktionerna som finns i Query Analyzer och hur de kan användas för att hjälpa till att finjustera prestanda i Transact-SQL kod.
Närhelst du skriver en fråga i Query Analyzer kan du sedan köra den och se resultatet direkt. Även om detta låter dig veta om resultatet är det du förväntade dig, så säger det inte mycket om prestandan. Visst, längst ner på skärmen kan du se hur länge frågan kördes, men det är i princip allt.
En av de mest kraftfulla finesserna i Query Analyzer är att du kan starta en funktion som kallas Show Execution Plan. Den låter dig se den egentliga exekveringsplanen som används av SQL Server´s Query Optimizer för att exekvera frågan. Alternativet återfinns i menyn Query under huvumenyn i Query Analyzer och måste aktiveras innan frågan exekveras. När frågan väl är exekverad visas resultatet från exekveringsplanen grafiskt i ett separat fönster som du når via en flik under fönstret för frågeresultatet.
Exekveringsplanen som visas i Exekution Plan fönstret kan vara väldigt enkel om frågan är enkel, eller väldigt komplex om frågan är komplex. Den visar dig steg-för-steg hur Qurey Optimizer exekverade frågan. Exekveringsplanen ska läsas från höger till vänster då den högra delen av planen indikerar första steget som utfördes av Query Analyzer. Du fortsätter läsa planen från höger till vänster tills du kommer till vänstra delen av skärmen som indikerar det sista steget som Query Analyzer utförde.
Även om den grafiska presentationen av exekveringsplanen är intressant så är den mest användbara och kraftfulla delen av detta verktyget svår att hitta. Om du flyttar muspekaren till toppen på varje steg i planen visas ett popup fönster med detaljerad information som exakt beskriver vad Query Opitmizer utförde under varje steg då frågan exekverades.
Informationen som visas i popup fönstret är ibland uppenbar, till exempel att den berättar för dig att ”Clustered Index Scan” utförts, eller så är den väldigt otydlig, till exempel “Subtree Cost” var .0376. Detaljerna behöver inte bara ytterligare förklaring, de behöver tolkas. Förklaringar kan hittas i Book Online, men tolkningen kräver erfarenhet av att använda och finjustera prestanda i SQL Server, ett ämne som inte berörs i denna artikeln.
Om frågan du arbetar med tar lång tid att köra och du vill experimentera med din fråga behöver du inte köra den varje gång. Query Analyzer har även ett alternativ för att skapa och visa en Execution Plan utan att egentligen köra frågan. Detta alternativet återfinns också under menyn Query och heter ”Display Estimated Plan”.
När detta alternativet är valt skapar och visar Query Optimizer exekveringsplanen, men kör den inte. Notera att detta refereras till som en ”estimated” exekveringsplan, alltså en uppskattad plan. Det betyder att resultatet av planen inte exakt behöver vara den som som Query Optimizer kommer att använda om frågan verkligen körs. Dock kommer den att ligga nära, och det är ett bra verktyg om du experimenterar mycket med frågor som körs länge. När du väl har finjusterat frågan genom att använda denna funktionen, kan du stänga av den och köra frågan på riktigt och se hur det verkligen utförs.
Show Server Trace kan användas som hjälpmedel för att finjustera prestandan i frågor, Stored procedures eller Transact-SQL skript. Vad den gör är att visa kommunikationen mellan Query Analyzer (som agerar som en SQL Server klient) och SQL Server. Detta är samma typ av information som fångas av SQL Server 2000 Profiler vilken beskrivs längre fram i artikeln. Den stora skillnaden är att du bara ser meddelanden till SQL Server som skapas av frågan eller scriptet som du kör i Query Analyzer.
Show Server Trace funktionen startas från menyn Query under huvudmenyn i Query Analyzer och måste aktiveras innan frågan körs, resultatet av spårningen visas i ett separat fönster som du når via en flik under fönstret för frågeresultatet.
Resultatet av spårningen presenteras i form av rader, där varje rad representerar ett distinkt meddelande från Query Analyzer till SQL Server. Varje rad innehåller texten för meddelandet som exempelvis Transact-SQL kod; Event Class som beskriver vilken typ av meddelande som skickats, varaktigheten av kommunikationen, hur mycket CPU tid som använts och hur många läsningar eller skrivningar som utförts för händelsen. Denna informationen kan vara väldigt värdefull när man analyserar frågeprestanda och när man jämför prestanda på en variant av frågan gentemot en annan.
Liksom Show Server Trace funktionen, så kan Show Client Statistics användas som hjälpmedel för att finjustera prestandan i frågor, Stored procedures och skript. Det här alternativet förser dig med applikationsfil, nätverk och tidsstatistik för vilken som helst Transact-SQL som du kör i Query Analyzer. Denna statistik ger dig ytterligare information som du kan använda för att se hur pass effektivt frågan körs, och låter dig också på ett enkelt sätt jämföra en fråga gentemot en annan.
Show Client Statistics funktionen startas från menyn Query under huvudmenyn i Query Analyzer och måste aktiveras innan frågan körs. När frågan väl är exekverad visas resultatet av spårningen i ett separat fönster som du når via en flik under fönstret för frågeresultatet.
Resultatet av Show Client Statistics består av en serie av tre tabeller där var och en innehåller en uppsättning statistik om hur frågan utfördes. Statistiken kan innehålla exempelvis:
Samt många fler. Denna statistik kan vara värdefull i kartläggningen för hur en specifik fråga körs och hjälpa dig att diagnostisera prestandarelaterade problem.
SQL Analyzer Manage Index verktyget är inte designat för att hjälpa dig att diagnostisera prestanda, men den låter dig experimentera med dina tabellindex när du finjusterar dina frågor med de andra verktyg som vi beskrivit tidigare. Manage Index alternativet återfinns under menyn Tools och låter dig lägga till, editera eller radera index på vilken tabell som helst. På så sätt kan du experimentera med indexering och testa dina index på ett och samma ställe.
Utan att du behöver göra någonting så skapar och bibehåller SQL Server automatiskt intern data på samtliga rader i alla dina tabeller. Denna statistik används sedan av Query Optimizer för att välja en optimal exekveringsplan av Transact-SQL kod. För det mesta gör SQL Server ett utmärkt jobb med att bibehålla denna statistik och Query Optimizer har nödvändig information som den behöver för att utföra sin uppgift.
Men ibland så är statistiken som SQL Server skapar inte optimal och det är detta Manage Statistics Tool är till för. Detta verktyg, som återfinns under menyn Tools i Query Analyzer, ger dig möjlighet att modifiera hur SQL Server automatiskt skapar och bibehåller statistik. Du kan lägga till, editera eller radera den statistik som bibehålls av SQL Server. Eftersom detta verktyg är tillgängligt från Query Analyzer kan du experimentera med olika uppsättningar av statistik och se hur det påverkar exekveringsplanen i Query Optimizer.
Såvida du inte är väldigt erfaren SQL Server databasadministratör/utvecklare så skulle jag inte rekommendera att använda detta verktyget. Att välja rätt statistik är en svår uppgift och din tid är troligtvis bättre spenderad att finjustera SQL Server prestanda på annat sätt.
Längre fram i denna artikel kommer du att lära dig mer om verktyget Index Tuning Wizard som kan användas till att rekommendera optimala index till en hel databas. Men för tillfället behöver du känna till att Index Tuning Wizard även kan köras från Query Analyzer och där användas till att rekommendera index för specifika frågor.
Till exempel, låt oss säga att du utvärderar prestanda för en speciell fråga, men du är inte säker på om de index som för närvarande körs på tabellerna som berörs av frågan är effektiva för just denna specifika fråga. Vad du då kan göra är att köra Index Tuning Wizard (som återfinns under menyn Query i Query Analyzer), den utvärderar frågan och, om det är lämpligt, rekommenderar att ett nytt index läggs till för att optimera just denna specifika fråga.
Samtidigt som detta kan vara ett användbart verktyg är det också lite farligt. Detta för att det bara utvärderar index för en specifik fråga. Det tar inte hänsyn till andra frågor som eventuellt körs mot tabellerna, eller den inverkan det kan ha att lägga till index på INSERT, UPDATE eller DELETE mot dessa tabellerna.
I många fall är det bättre att använda Index Tuning Wizard till att utvärdera en hel databas åt gången istället för enstaka frågor. På så sätt kan Index Tuning Wizard sörja för mer balanserade förslag.
Som du kan se är SQL Server 2000 Query Analyzer ett kraftfullt verktyg med många funktioner och vi har nätt och jämt skrapat på ytan av hur du kan använda det för att finjustera prestanda i Transact-SQL kod. Du kommer att upptäcka att det är värt att ta den tid som krävs för att du ska lära dig att behärska detta viktiga redskap.
SQL Server Profiler är ett kraftfullt verktyg för att hjälpa till att hitta SQL Server prestandaproblem, men det är inget verktyg för nybörjare. I huvudsak så låter det dig fånga kommunikationen mellan din applikation och SQL Server. Det är inte det egentliga fångandet av data som är svårt, utan det som ibland kan vara svårt för en nybörjare är att tolka den data som fångats in.
I denna sektionen ska vi ta en titt på vad Profiler kan göra och även lära lite grand om hur Profiler kan användas för att hjälpa till att identifiera och lösa prestanda problem.
SQL Server kan fånga nästan all kommunikation mellan en SQL Server och vilken som helst annan applikation. De olika meddelandena som du kan fånga refereras till som händelser (events) och är grupperade i Event Classes. Varje Event Class inkluderar en eller flera specifika händelser. Till exempel, Event Class "Performance" har åtta händelser bland annat Execution Plan och Show Plan Statistics. Profiler erbjuder dig 13 olika Event Classes att välja emellan.
Varje händelse inkluderar en varierande mängd datakolumner data som är associerade till dem. Till exempel, NTUserName eller ApplicationName är bara två av alla kolumner som innehåller information om olika händelser som du kan fånga.
I ett produktionssystem kan det förekomma tusentals händelser per sekund, mer än du någonsin kan försöka dig på att analysera. För att göra analysen , du kan välja att endast fånga händelser mellan en specifik användare och SQL Server, eller från en specifik applikation och SQL Server, eav data i Profiler lite enklare har Profiler förmågan att filtrera ut endast de händelser du är intresserad av. Till exempelller från en specifik databas som finns på SQL Server. Du kan också välja vilka händelser du vill fånga och vilka du vill fånga för varje händelse. Detta hjälper dig att minska mängden av fångad data. En stor del av att lära sig att använda Profiler ligger i att kunna avgöra vilka händelser och datakolumner du ska fånga och vilka du inte ska fånga för analys.
För att gör livet lite enklare har Profiler förmågan att skapa vad som kallas Trace Templates, detta är filer som låter dig spara olika spårningar som du skapar så du kan använda dem om och om igen. Detta kan vara väldigt tidsbesparande om det är en komplicerad spårning och du vill köra den flera gånger.
När du skapar och sparar en Trace Template kan du köra den när som helst. Resultatet av spårningen (händelserna du fångar) kan visas och kasseras, sparas som en spårningsfil (trace file), eller sparas i en SQL Server-tabell. Att spara dina spårningsresultat kan vara behändigt då du när som helst kan gå tillbaka och titta på dem igen.
När en fråga körts kan du se resultaten (visa händelserna du fångat) genom att använda Profiler. I Profiler fönstret kan du titta på varje händelse och de datakolumner du fångat rad för rad. I många fall inkluderar händelserna Transact-SQL kod som du kan se direkt genom att använda Profiler, eller så kan du klippa ut och klistra in dem i Query Analyzer för en mer detaljerad analys.
Att skapa en spårning för första gången kan vara svårt om du inte är bekant alla olika variationer av händelser och datakolumner. Det enklaste sättet att börja lära sej använda Profiler är att använda Profiler’s Create Trace Wizard. Detta verktyget inkluderar en mängd grundläggande mallar som du kan skrädarsy för specifika behov. Till exempel, ”Find the Worst Performing Queries”-mallen kan användas till att identifiera de frågor som tar längre tid att köra än en förbestämd tid, exempelvis en fråga som tar längre tid än en sekund. Det finns en mängd av dessa mallar tillgängliga i Create Trace Wizard och du bör utforska dem allihopa.
Profiler är ett kraftfullt verktyg för att identifiera prestanda relaterade problem och kan användas när som helst under utvecklingsprocessen. Ett av områdena då jag tycker det är synnerligen användbart är när man ska lokalisera och justera prestanda i existerade applikationer. Som du vet så rekommenderar jag starkt att man tar hänsyn till prestandan tidigt i designfasen av applikationen för att undvika prestandaproblem, men det är inte alltid det är möjligt.
Till exempel, du kan ha övertagit en applikation som utvecklats inom företaget, eller kanske ditt företag har köpt en extern applikation, som använder sig av SQL Server. Då kan det hända att du kallas in för att testa och lösa applikationsrelaterade prestandaproblem.
Ett av de första problemen med att lokalisera och justera problem i applikationer du inte är bekant med är att inte veta hur den fungerar. Även om det blir lite monotont kan du använda Profiler till att ”tjuvtitta” på hur din applikation kommunicerar med SQL Server. Du kan konfigurera en spårning i Profiler till att fånga all kommunikation mellan applikationen och SQL Server. Sedan, ett steg i taget, kan du genomföra några uppgifter i applikationen, och därefter granska kommunikationen mellan applikationen och SQL Server för att få reda på hur de båda kommunicerar.
Att tolka kommunikationen kräver oftast gedigen förståelse för Transact-SQL, men om du vet vad du gör kan du komma underfund med hur en applikation arbetar mot SQL-Server. Du behöver inte analysera all kommunikation mellan applikationen och SQL-Server, utan kan fokusera dig på de områden av applikationen som verkar orsaka prestandaproblemen, som ett specifikt meddelande eller någon uppdateringsprocess.
Generellt sett, när du väl skapat en spårning av den operation som skapar problem kan du granska Transact-SQL i spårningen och identifiera problemet. Till exempel, jag analyserade en speciell applikation som utvecklats inom företaget och upptäckte att problemet var att VB koden som användes för att komma åt SQL-Server data skapade pekare och hämtade sedan bara en rad åt gången från SQL-Server till applikationen. Problemet var att det blev flera miljoner rader som skickades vilket sänkte prestandan. När jag väl identifierat orsaken till problemet kunde jag få VB koden omskriven.
En annan funktion i Profiler är att du kan skapa spårningar av din applikations aktivitet med SQL-Server och sen använda denna spårning som ingångsdata till Index Wizard. Index Wizard analyserar sedan aktiviteten och kan ge en rekommendation om klustrade och icke-klustrade index ska läggas till eller tas bort för att öka databasens prestanda. Du kommer att lära dig mer om Index Wizard i nästa sektion av denna artikeln.
Som du märker är Profiler ett väldigt kraftfullt verktyg, ett verktyg som varje SQL-Server DBA behöver lära sig och behärska.
SQL Server Index Wizard är ett verktyg som du snabbt kommer att tycka mer och mer om. Även om det inte är perfekt, har detta verktyget förmågan att utvärdera riktiga produktionsfrågor som körs mot SQL Server, och baserat på egenskaperna hos dessa frågor, föreslå specifika klustrade och icke-klustrade index som bör läggas till i din databas tabeller. Index Wizard kan köras från menyn Tools i SQL Server 2000 Profiler.
Detta verktyg kan användas under testning av din databas vid tidiga faser i din applikationsutveckling, och det kan användas efter det att databasen tagits i bruk. Faktum är, att du bör överväga att köra Index Wizard på din databas då och då även efter det att din applikation tagits i bruk. Detta eftersom databasens användningsförlopp kan variera under tidens gång och de optimala indexen för din databas kan förändras vartefter hur databasen verkligen används.
Även om Index Wizard är ett utmärkt verktyg ska du inte förlita dig enbart på det för att rekommendera index till din databas. Fastän att Index Wizard är väldigt ”smart” kan det inte göra bättre val än en DBA som är rutinerad i index justering.
Den bästa funktionen i Index Wizard är att den kan arbeta med riktig data från din SQL Server produktionsdatabas istället för påhittad eller simulerad data. Det betyder att indexen som rekommenderas av den är skräddarsydda till hur din databas verkligen används. Så här fungerar det.
Innan du kan använda Index Wizard måste du skapa vad som kallas för en arbetsbörda (workload). En arbetsbörda är en spårning i Profiler eller ett Transact-SQL script. I de flesta fallen vill du hellre använda Profilers spårningar då de reflekterar egentlig databasaktivitet.
Om du vill att Index Wizard ska producera användbara resultat är det viktigt att arbetsbördan skapas under en tidsperiod som representerar typiskt ”vardags” databasanvändande. På så vis kan Index Wizard ge förslag baserade på verklig användning av din databas.
När en arbetsbörda väl skapats kan det matas in i Index Wizard. Vad Index Wizard gör är att ta stickprov på frågorna den hittar i arbetsbördan och analyserar dem genom att använda SQL Server Query Optimizer.
När Index Wizard har analyserat en arbetsbörda (är den stor kan det ta flera timmar att köra) kommer den att föreslå vad den anser vara den bästa mixen av klustrade och icke-klustrade index för tabellerna i din databas, baserat på arbetsbördan den analyserade. Dessutom, om du redan har index i din databas och Index Wizard upptäcker att de inte används kan den föreslå att de plockas bort.
När Index Wizard skapar sina rekommendationer har du möjligheten att låta att Index Wizard gör dem direkt, (rekommenderas inte för produktionsdatabaser) att schemalägga deras skapande vid ett senare tillfälle eller att spara dem som script. Jag rekommenderar att du sparar dem som script vilket ger dig möjlighet att ta dig tid och gå tillbaka och studera förslagen. När du är nöjd med förslagen kan du köra dem genom att använda SQL Server Query Analyzer vid lämpligt tillfälle. Om du inte samtycker till förslagen kan du lätt göra de förändringar du anser nödvändiga i scriptet innan du kör det.
System Monitor (Systemövervakaren) är inte ett verktyg som ingår I SQL Server 2000, utan ett verktyg som är inkluderat I Windows 2000. System Monitor ger dig möjlighet att övervaka prestanda i både Windows 2000 och SQL Server 2000. Det är dessutom ett mycket kraftfullt verktyg för analys och övervakning. Programmet kan köras av alla med administrativa rättigheter i Windows 2000 på en SQL server. Att enbart ha administrativa rättigheter I SQL Server 2000 räcker inte för att köra System Monitor.
System Monitor kan övervaka flera hundra indikatorer, eller räknare (hänvisas till som “counters”) och över 110 SQL Server 2000 indikatorer (counters), vilket är mer än tillräckligt för att monitorera och övervaka prestanda i SQL Server 2000.
Även då System Monitor ger dig ett överflöd av ”counters” att mäta, så vill man i de flesta fall bara övervaka ett fåtal, och spara de mer obskyra till speciella situationer. Ibland kan man tro att man bara behöver övervaka SQL Server 2000 ”counters”, och inte Windows 2000, men så är oftast inte fallet. I de flesta fall kommer du troligen spendera mer tid att övervaka Windows 2000 , än SQL Server. Detta bror på att prestandan i SQL Server 2000 är helt beroende av prestandan i Windows 2000.
Som vi tidigare nämnt, så finns det en handfull Windows 2000- och SQL Server 2000-räknare som du kan komma att vilja titta på regelbundet. Nedan följer några som kan hjälpa till att identifiera potentiella prestandaproblem.
Dessa är bara ett fåtal av de räknare i Windows 2000 och SQL Server 2000, som du kan övervaka och använda för att kunna finjustera prestandan i din SQL Server-baserade applikation.
I huvudsak erbjuder System Monitor två olika sätt att samla in och analysera Windows 2000- och SQL Server 2000-räknare. Du kan både samla in och rita upp grafer i realtid, eller samla data i logfiler och rita upp grafer i efterhand. Att samla in data och analysera i realtid är bäst när du mäter på din server och vill ha resultatet omedelbart. Det är även praktiskt när man felsöker specifika prestandarelaterade problem. Realtidsläget samlar in data, som standard varje sekund, och visar det i en graf i takt med att datan samlas in. Du kan samla in och rita upp flera olika räknare samtidigt. Detta kan vara väldigt användbart eftersom det ofta är viktigt att se hur olika räknare påverkar varandra.
Även om realtidsanalys ofta är praktiskt, är det i allmänhet mer användbart att samla in data under en längre tidsperiod och senare analysera den när det passar. System Monitor låter dig välja vilka räknare du är intresserad av, och hur ofta den skall samla in dem. T. Ex, du kanske vill samla in data från 20 räknare, var 60e sekund under 24 timmar. Eller du kanske vill samla in data från 50 räknare, var 600e sekund under 30 dagar. När datan väl är insamlad kan du analysera den i form av grafer via System Monitor, eller så kan du exportera datan till en databas eller kalkylark för en mer detaljerad analys.
Om du tar allvarligt på att monitorera prestandan på din SQL Server rekommenderar jag att du kontinuerligt samlar in data på nyckel-räknarna, och sen gör en trendanalys (kan göras i Excel) för att hitta trender i serverns beteende. Genom detta förfarande kan du förekomma serverns behov för flera CPUer, snabbare I/O eller mer minne. Trendanalys låter dig projicera historisk data i framtiden, vilket ger ett bra underlag att visa upp för din chef när du skall motivera uppgraderingar av din nuvarande hårdvara, eller byta ut hårdvara i dina servrar.
System Monitor är ett kraftfullt verktyg, och du kommer att behöva ta dig tid att lära dig behärska det. Du kommer att finna det mycket praktiskt för att hitta och identifiera prestandaproblem och att hjälpa dig beräkna framtida behov för hårdvara.
SQL Server 2000 Query Analyzer
SQL Server 2000 Query Analyzer är inte bara ett utmärkt verktyg för att utveckla och debugga Transact-SQL kod, utan även för att finjustera prestanda i Transact-SQL kod. I detta avsnitt ska vi titta på vad Query Analyser kan göra och även lära oss mer om hur det kan användas för att hjälpa till att identifiera och lösa prestanda problem. Liksom för många av de mer avancerade SQL Server 2000 verktygen behöver du ha ganska god kännedom och förståelse om Transact-SQL för att få ut så mycket som möjligt av verktyget. Låt oss titta på några av nyckelfunktionerna som finns i Query Analyzer och hur de kan användas för att hjälpa till att finjustera prestanda i Transact-SQL kod.
Show Execution Plan
Närhelst du skriver en fråga i Query Analyzer kan du sedan köra den och se resultatet direkt. Även om detta låter dig veta om resultatet är det du förväntade dig, så säger det inte mycket om prestandan. Visst, längst ner på skärmen kan du se hur länge frågan kördes, men det är i princip allt.En av de mest kraftfulla finesserna i Query Analyzer är att du kan starta en funktion som kallas Show Execution Plan. Den låter dig se den egentliga exekveringsplanen som används av SQL Server´s Query Optimizer för att exekvera frågan. Alternativet återfinns i menyn Query under huvumenyn i Query Analyzer och måste aktiveras innan frågan exekveras. När frågan väl är exekverad visas resultatet från exekveringsplanen grafiskt i ett separat fönster som du når via en flik under fönstret för frågeresultatet.
Exekveringsplanen som visas i Exekution Plan fönstret kan vara väldigt enkel om frågan är enkel, eller väldigt komplex om frågan är komplex. Den visar dig steg-för-steg hur Qurey Optimizer exekverade frågan. Exekveringsplanen ska läsas från höger till vänster då den högra delen av planen indikerar första steget som utfördes av Query Analyzer. Du fortsätter läsa planen från höger till vänster tills du kommer till vänstra delen av skärmen som indikerar det sista steget som Query Analyzer utförde.
Även om den grafiska presentationen av exekveringsplanen är intressant så är den mest användbara och kraftfulla delen av detta verktyget svår att hitta. Om du flyttar muspekaren till toppen på varje steg i planen visas ett popup fönster med detaljerad information som exakt beskriver vad Query Opitmizer utförde under varje steg då frågan exekverades.
Informationen som visas i popup fönstret är ibland uppenbar, till exempel att den berättar för dig att ”Clustered Index Scan” utförts, eller så är den väldigt otydlig, till exempel “Subtree Cost” var .0376. Detaljerna behöver inte bara ytterligare förklaring, de behöver tolkas. Förklaringar kan hittas i Book Online, men tolkningen kräver erfarenhet av att använda och finjustera prestanda i SQL Server, ett ämne som inte berörs i denna artikeln.
Om frågan du arbetar med tar lång tid att köra och du vill experimentera med din fråga behöver du inte köra den varje gång. Query Analyzer har även ett alternativ för att skapa och visa en Execution Plan utan att egentligen köra frågan. Detta alternativet återfinns också under menyn Query och heter ”Display Estimated Plan”.
När detta alternativet är valt skapar och visar Query Optimizer exekveringsplanen, men kör den inte. Notera att detta refereras till som en ”estimated” exekveringsplan, alltså en uppskattad plan. Det betyder att resultatet av planen inte exakt behöver vara den som som Query Optimizer kommer att använda om frågan verkligen körs. Dock kommer den att ligga nära, och det är ett bra verktyg om du experimenterar mycket med frågor som körs länge. När du väl har finjusterat frågan genom att använda denna funktionen, kan du stänga av den och köra frågan på riktigt och se hur det verkligen utförs.
Show Server Trace
Show Server Trace kan användas som hjälpmedel för att finjustera prestandan i frågor, Stored procedures eller Transact-SQL skript. Vad den gör är att visa kommunikationen mellan Query Analyzer (som agerar som en SQL Server klient) och SQL Server. Detta är samma typ av information som fångas av SQL Server 2000 Profiler vilken beskrivs längre fram i artikeln. Den stora skillnaden är att du bara ser meddelanden till SQL Server som skapas av frågan eller scriptet som du kör i Query Analyzer.Show Server Trace funktionen startas från menyn Query under huvudmenyn i Query Analyzer och måste aktiveras innan frågan körs, resultatet av spårningen visas i ett separat fönster som du når via en flik under fönstret för frågeresultatet.
Resultatet av spårningen presenteras i form av rader, där varje rad representerar ett distinkt meddelande från Query Analyzer till SQL Server. Varje rad innehåller texten för meddelandet som exempelvis Transact-SQL kod; Event Class som beskriver vilken typ av meddelande som skickats, varaktigheten av kommunikationen, hur mycket CPU tid som använts och hur många läsningar eller skrivningar som utförts för händelsen. Denna informationen kan vara väldigt värdefull när man analyserar frågeprestanda och när man jämför prestanda på en variant av frågan gentemot en annan.
Show Client Statistics
Liksom Show Server Trace funktionen, så kan Show Client Statistics användas som hjälpmedel för att finjustera prestandan i frågor, Stored procedures och skript. Det här alternativet förser dig med applikationsfil, nätverk och tidsstatistik för vilken som helst Transact-SQL som du kör i Query Analyzer. Denna statistik ger dig ytterligare information som du kan använda för att se hur pass effektivt frågan körs, och låter dig också på ett enkelt sätt jämföra en fråga gentemot en annan.Show Client Statistics funktionen startas från menyn Query under huvudmenyn i Query Analyzer och måste aktiveras innan frågan körs. När frågan väl är exekverad visas resultatet av spårningen i ett separat fönster som du når via en flik under fönstret för frågeresultatet.
Resultatet av Show Client Statistics består av en serie av tre tabeller där var och en innehåller en uppsättning statistik om hur frågan utfördes. Statistiken kan innehålla exempelvis:
- Antal rader berörda av INSERT, UPDATE och DELETE uttryck
- Antal rader berörda av SELECT uttryck
- Antalet användartransaktioner
- Antalet server anrop
- Hur många bytes som skickats
- Ackumulerad processtid för klienten
Samt många fler. Denna statistik kan vara värdefull i kartläggningen för hur en specifik fråga körs och hjälpa dig att diagnostisera prestandarelaterade problem.
Manage Indexes
SQL Analyzer Manage Index verktyget är inte designat för att hjälpa dig att diagnostisera prestanda, men den låter dig experimentera med dina tabellindex när du finjusterar dina frågor med de andra verktyg som vi beskrivit tidigare. Manage Index alternativet återfinns under menyn Tools och låter dig lägga till, editera eller radera index på vilken tabell som helst. På så sätt kan du experimentera med indexering och testa dina index på ett och samma ställe.
Hantera statistik
Utan att du behöver göra någonting så skapar och bibehåller SQL Server automatiskt intern data på samtliga rader i alla dina tabeller. Denna statistik används sedan av Query Optimizer för att välja en optimal exekveringsplan av Transact-SQL kod. För det mesta gör SQL Server ett utmärkt jobb med att bibehålla denna statistik och Query Optimizer har nödvändig information som den behöver för att utföra sin uppgift.Men ibland så är statistiken som SQL Server skapar inte optimal och det är detta Manage Statistics Tool är till för. Detta verktyg, som återfinns under menyn Tools i Query Analyzer, ger dig möjlighet att modifiera hur SQL Server automatiskt skapar och bibehåller statistik. Du kan lägga till, editera eller radera den statistik som bibehålls av SQL Server. Eftersom detta verktyg är tillgängligt från Query Analyzer kan du experimentera med olika uppsättningar av statistik och se hur det påverkar exekveringsplanen i Query Optimizer.
Såvida du inte är väldigt erfaren SQL Server databasadministratör/utvecklare så skulle jag inte rekommendera att använda detta verktyget. Att välja rätt statistik är en svår uppgift och din tid är troligtvis bättre spenderad att finjustera SQL Server prestanda på annat sätt.
Index Tuning Wizard (för individuella frågor)
Längre fram i denna artikel kommer du att lära dig mer om verktyget Index Tuning Wizard som kan användas till att rekommendera optimala index till en hel databas. Men för tillfället behöver du känna till att Index Tuning Wizard även kan köras från Query Analyzer och där användas till att rekommendera index för specifika frågor.Till exempel, låt oss säga att du utvärderar prestanda för en speciell fråga, men du är inte säker på om de index som för närvarande körs på tabellerna som berörs av frågan är effektiva för just denna specifika fråga. Vad du då kan göra är att köra Index Tuning Wizard (som återfinns under menyn Query i Query Analyzer), den utvärderar frågan och, om det är lämpligt, rekommenderar att ett nytt index läggs till för att optimera just denna specifika fråga.
Samtidigt som detta kan vara ett användbart verktyg är det också lite farligt. Detta för att det bara utvärderar index för en specifik fråga. Det tar inte hänsyn till andra frågor som eventuellt körs mot tabellerna, eller den inverkan det kan ha att lägga till index på INSERT, UPDATE eller DELETE mot dessa tabellerna.
I många fall är det bättre att använda Index Tuning Wizard till att utvärdera en hel databas åt gången istället för enstaka frågor. På så sätt kan Index Tuning Wizard sörja för mer balanserade förslag.
Lägg ner lite tid på att behärska Query Analyzer
Som du kan se är SQL Server 2000 Query Analyzer ett kraftfullt verktyg med många funktioner och vi har nätt och jämt skrapat på ytan av hur du kan använda det för att finjustera prestanda i Transact-SQL kod. Du kommer att upptäcka att det är värt att ta den tid som krävs för att du ska lära dig att behärska detta viktiga redskap.SQL Server 2000 Profiler
SQL Server Profiler är ett kraftfullt verktyg för att hjälpa till att hitta SQL Server prestandaproblem, men det är inget verktyg för nybörjare. I huvudsak så låter det dig fånga kommunikationen mellan din applikation och SQL Server. Det är inte det egentliga fångandet av data som är svårt, utan det som ibland kan vara svårt för en nybörjare är att tolka den data som fångats in.
I denna sektionen ska vi ta en titt på vad Profiler kan göra och även lära lite grand om hur Profiler kan användas för att hjälpa till att identifiera och lösa prestanda problem.
Funktioner i Profiler
SQL Server kan fånga nästan all kommunikation mellan en SQL Server och vilken som helst annan applikation. De olika meddelandena som du kan fånga refereras till som händelser (events) och är grupperade i Event Classes. Varje Event Class inkluderar en eller flera specifika händelser. Till exempel, Event Class "Performance" har åtta händelser bland annat Execution Plan och Show Plan Statistics. Profiler erbjuder dig 13 olika Event Classes att välja emellan.Varje händelse inkluderar en varierande mängd datakolumner data som är associerade till dem. Till exempel, NTUserName eller ApplicationName är bara två av alla kolumner som innehåller information om olika händelser som du kan fånga.
I ett produktionssystem kan det förekomma tusentals händelser per sekund, mer än du någonsin kan försöka dig på att analysera. För att göra analysen , du kan välja att endast fånga händelser mellan en specifik användare och SQL Server, eller från en specifik applikation och SQL Server, eav data i Profiler lite enklare har Profiler förmågan att filtrera ut endast de händelser du är intresserad av. Till exempelller från en specifik databas som finns på SQL Server. Du kan också välja vilka händelser du vill fånga och vilka du vill fånga för varje händelse. Detta hjälper dig att minska mängden av fångad data. En stor del av att lära sig att använda Profiler ligger i att kunna avgöra vilka händelser och datakolumner du ska fånga och vilka du inte ska fånga för analys.
För att gör livet lite enklare har Profiler förmågan att skapa vad som kallas Trace Templates, detta är filer som låter dig spara olika spårningar som du skapar så du kan använda dem om och om igen. Detta kan vara väldigt tidsbesparande om det är en komplicerad spårning och du vill köra den flera gånger.
När du skapar och sparar en Trace Template kan du köra den när som helst. Resultatet av spårningen (händelserna du fångar) kan visas och kasseras, sparas som en spårningsfil (trace file), eller sparas i en SQL Server-tabell. Att spara dina spårningsresultat kan vara behändigt då du när som helst kan gå tillbaka och titta på dem igen.
När en fråga körts kan du se resultaten (visa händelserna du fångat) genom att använda Profiler. I Profiler fönstret kan du titta på varje händelse och de datakolumner du fångat rad för rad. I många fall inkluderar händelserna Transact-SQL kod som du kan se direkt genom att använda Profiler, eller så kan du klippa ut och klistra in dem i Query Analyzer för en mer detaljerad analys.
Att skapa en spårning för första gången kan vara svårt om du inte är bekant alla olika variationer av händelser och datakolumner. Det enklaste sättet att börja lära sej använda Profiler är att använda Profiler’s Create Trace Wizard. Detta verktyget inkluderar en mängd grundläggande mallar som du kan skrädarsy för specifika behov. Till exempel, ”Find the Worst Performing Queries”-mallen kan användas till att identifiera de frågor som tar längre tid att köra än en förbestämd tid, exempelvis en fråga som tar längre tid än en sekund. Det finns en mängd av dessa mallar tillgängliga i Create Trace Wizard och du bör utforska dem allihopa.
Hur man använder Profiler för att finjustera prestanda
Profiler är ett kraftfullt verktyg för att identifiera prestanda relaterade problem och kan användas när som helst under utvecklingsprocessen. Ett av områdena då jag tycker det är synnerligen användbart är när man ska lokalisera och justera prestanda i existerade applikationer. Som du vet så rekommenderar jag starkt att man tar hänsyn till prestandan tidigt i designfasen av applikationen för att undvika prestandaproblem, men det är inte alltid det är möjligt.Till exempel, du kan ha övertagit en applikation som utvecklats inom företaget, eller kanske ditt företag har köpt en extern applikation, som använder sig av SQL Server. Då kan det hända att du kallas in för att testa och lösa applikationsrelaterade prestandaproblem.
Ett av de första problemen med att lokalisera och justera problem i applikationer du inte är bekant med är att inte veta hur den fungerar. Även om det blir lite monotont kan du använda Profiler till att ”tjuvtitta” på hur din applikation kommunicerar med SQL Server. Du kan konfigurera en spårning i Profiler till att fånga all kommunikation mellan applikationen och SQL Server. Sedan, ett steg i taget, kan du genomföra några uppgifter i applikationen, och därefter granska kommunikationen mellan applikationen och SQL Server för att få reda på hur de båda kommunicerar.
Att tolka kommunikationen kräver oftast gedigen förståelse för Transact-SQL, men om du vet vad du gör kan du komma underfund med hur en applikation arbetar mot SQL-Server. Du behöver inte analysera all kommunikation mellan applikationen och SQL-Server, utan kan fokusera dig på de områden av applikationen som verkar orsaka prestandaproblemen, som ett specifikt meddelande eller någon uppdateringsprocess.
Generellt sett, när du väl skapat en spårning av den operation som skapar problem kan du granska Transact-SQL i spårningen och identifiera problemet. Till exempel, jag analyserade en speciell applikation som utvecklats inom företaget och upptäckte att problemet var att VB koden som användes för att komma åt SQL-Server data skapade pekare och hämtade sedan bara en rad åt gången från SQL-Server till applikationen. Problemet var att det blev flera miljoner rader som skickades vilket sänkte prestandan. När jag väl identifierat orsaken till problemet kunde jag få VB koden omskriven.
En annan funktion i Profiler är att du kan skapa spårningar av din applikations aktivitet med SQL-Server och sen använda denna spårning som ingångsdata till Index Wizard. Index Wizard analyserar sedan aktiviteten och kan ge en rekommendation om klustrade och icke-klustrade index ska läggas till eller tas bort för att öka databasens prestanda. Du kommer att lära dig mer om Index Wizard i nästa sektion av denna artikeln.
Som du märker är Profiler ett väldigt kraftfullt verktyg, ett verktyg som varje SQL-Server DBA behöver lära sig och behärska.
Index Wizard
SQL Server Index Wizard är ett verktyg som du snabbt kommer att tycka mer och mer om. Även om det inte är perfekt, har detta verktyget förmågan att utvärdera riktiga produktionsfrågor som körs mot SQL Server, och baserat på egenskaperna hos dessa frågor, föreslå specifika klustrade och icke-klustrade index som bör läggas till i din databas tabeller. Index Wizard kan köras från menyn Tools i SQL Server 2000 Profiler.
Detta verktyg kan användas under testning av din databas vid tidiga faser i din applikationsutveckling, och det kan användas efter det att databasen tagits i bruk. Faktum är, att du bör överväga att köra Index Wizard på din databas då och då även efter det att din applikation tagits i bruk. Detta eftersom databasens användningsförlopp kan variera under tidens gång och de optimala indexen för din databas kan förändras vartefter hur databasen verkligen används.
Även om Index Wizard är ett utmärkt verktyg ska du inte förlita dig enbart på det för att rekommendera index till din databas. Fastän att Index Wizard är väldigt ”smart” kan det inte göra bättre val än en DBA som är rutinerad i index justering.
Den bästa funktionen i Index Wizard är att den kan arbeta med riktig data från din SQL Server produktionsdatabas istället för påhittad eller simulerad data. Det betyder att indexen som rekommenderas av den är skräddarsydda till hur din databas verkligen används. Så här fungerar det.
Innan du kan använda Index Wizard måste du skapa vad som kallas för en arbetsbörda (workload). En arbetsbörda är en spårning i Profiler eller ett Transact-SQL script. I de flesta fallen vill du hellre använda Profilers spårningar då de reflekterar egentlig databasaktivitet.
Om du vill att Index Wizard ska producera användbara resultat är det viktigt att arbetsbördan skapas under en tidsperiod som representerar typiskt ”vardags” databasanvändande. På så vis kan Index Wizard ge förslag baserade på verklig användning av din databas.
När en arbetsbörda väl skapats kan det matas in i Index Wizard. Vad Index Wizard gör är att ta stickprov på frågorna den hittar i arbetsbördan och analyserar dem genom att använda SQL Server Query Optimizer.
När Index Wizard har analyserat en arbetsbörda (är den stor kan det ta flera timmar att köra) kommer den att föreslå vad den anser vara den bästa mixen av klustrade och icke-klustrade index för tabellerna i din databas, baserat på arbetsbördan den analyserade. Dessutom, om du redan har index i din databas och Index Wizard upptäcker att de inte används kan den föreslå att de plockas bort.
När Index Wizard skapar sina rekommendationer har du möjligheten att låta att Index Wizard gör dem direkt, (rekommenderas inte för produktionsdatabaser) att schemalägga deras skapande vid ett senare tillfälle eller att spara dem som script. Jag rekommenderar att du sparar dem som script vilket ger dig möjlighet att ta dig tid och gå tillbaka och studera förslagen. När du är nöjd med förslagen kan du köra dem genom att använda SQL Server Query Analyzer vid lämpligt tillfälle. Om du inte samtycker till förslagen kan du lätt göra de förändringar du anser nödvändiga i scriptet innan du kör det.
System (Performance) Monitor
System Monitor (Systemövervakaren) är inte ett verktyg som ingår I SQL Server 2000, utan ett verktyg som är inkluderat I Windows 2000. System Monitor ger dig möjlighet att övervaka prestanda i både Windows 2000 och SQL Server 2000. Det är dessutom ett mycket kraftfullt verktyg för analys och övervakning. Programmet kan köras av alla med administrativa rättigheter i Windows 2000 på en SQL server. Att enbart ha administrativa rättigheter I SQL Server 2000 räcker inte för att köra System Monitor.
System Monitor kan övervaka flera hundra indikatorer, eller räknare (hänvisas till som “counters”) och över 110 SQL Server 2000 indikatorer (counters), vilket är mer än tillräckligt för att monitorera och övervaka prestanda i SQL Server 2000.
Även då System Monitor ger dig ett överflöd av ”counters” att mäta, så vill man i de flesta fall bara övervaka ett fåtal, och spara de mer obskyra till speciella situationer. Ibland kan man tro att man bara behöver övervaka SQL Server 2000 ”counters”, och inte Windows 2000, men så är oftast inte fallet. I de flesta fall kommer du troligen spendera mer tid att övervaka Windows 2000 , än SQL Server. Detta bror på att prestandan i SQL Server 2000 är helt beroende av prestandan i Windows 2000.
Vilka ”counters” skall du titta på?
Som vi tidigare nämnt, så finns det en handfull Windows 2000- och SQL Server 2000-räknare som du kan komma att vilja titta på regelbundet. Nedan följer några som kan hjälpa till att identifiera potentiella prestandaproblem.- För att hjälpa till att identifiera potentiella problem med CPU-kapacitet: ”Windows 2000 System Object: % Total Processor Time counter” mäter genomsnittet för alla CPUer i din server. Detta är den viktigaste räknaren att titta på för att avgöra systemets CPU-belastning. Om denna räknare ligger över 80% under längre tid (ca 10 minuter) så kan du ha en flaskhals i och med din CPU. Om så är fallet har du att välja mellan att reducera lasten på servern, skaffa snabbare CPU eller flera CPUer till din server.
- För att hitta potentiella problem med minnets prestanda är ”Windows 2000 Memory Object: Pages/Sec counter” viktig att undersöka. Den mäter antalet sidor ”pages” per sekund som växlas ut från minnet till disken, eller växlas in i minnet från disken. Om man antar att SQL Server är den enda större applikationen som körs på servern, så bör denna räknare hålla sig nära noll, med undantag för vissa belastningstoppar, vilket är normalt. Om denna räknare överskrider 0 kontinuerligt (mer än ca 10 minuter), kan det finnas ett växlingsrelaterat problem. Dessa kan inträffa om man kör andra applikationer än SQL Server på servern, eller om man har stängt av den dynamiska minneskonfigureringen i SQL Server.
- För att hjälpa till att identifiera I/O-prestandarelaterade problem så är räknaren ”Windows 2000 PhysicalDisk Object: Avg. Disk Queue Length” kritisk att övervaka. Om denna räknaren överstiger 2 under längre perioder (mer än 10 minuter) för var disk i ett system, så har man troligen en I/O-flaskhals för det systemet. Olika sätt att lösa detta problem är: Att lägga till diskar i systemet (om möjligt), skaffa snabbare diskar, lägga till chache-minne på diskkontrollern (om möjligt), använda en annan nivå av RAID, skaffa en snabbare kontroller eller reducera lasten på SQL Server.
- För att avgöra om servern har tillräckligt med fysiskt minne (RAM) så behöver man övervaka räknaren ”SQL Server 2000 Buffer Managre Objekt: Buffer Cache Hit Ratio”. Denna räknare hur ofta SQL Server hämtar data från bufferten istället för disken. I OLTP-applikationer bör denna ligga över 90%. Om så inte är fallet behöver man installera mer RAM för att öka prestandan, eller minska lasten på SQL Server.
Dessa är bara ett fåtal av de räknare i Windows 2000 och SQL Server 2000, som du kan övervaka och använda för att kunna finjustera prestandan i din SQL Server-baserade applikation.
Hur man bäst drar nytta av System Monitor
I huvudsak erbjuder System Monitor två olika sätt att samla in och analysera Windows 2000- och SQL Server 2000-räknare. Du kan både samla in och rita upp grafer i realtid, eller samla data i logfiler och rita upp grafer i efterhand. Att samla in data och analysera i realtid är bäst när du mäter på din server och vill ha resultatet omedelbart. Det är även praktiskt när man felsöker specifika prestandarelaterade problem. Realtidsläget samlar in data, som standard varje sekund, och visar det i en graf i takt med att datan samlas in. Du kan samla in och rita upp flera olika räknare samtidigt. Detta kan vara väldigt användbart eftersom det ofta är viktigt att se hur olika räknare påverkar varandra.Även om realtidsanalys ofta är praktiskt, är det i allmänhet mer användbart att samla in data under en längre tidsperiod och senare analysera den när det passar. System Monitor låter dig välja vilka räknare du är intresserad av, och hur ofta den skall samla in dem. T. Ex, du kanske vill samla in data från 20 räknare, var 60e sekund under 24 timmar. Eller du kanske vill samla in data från 50 räknare, var 600e sekund under 30 dagar. När datan väl är insamlad kan du analysera den i form av grafer via System Monitor, eller så kan du exportera datan till en databas eller kalkylark för en mer detaljerad analys.
Om du tar allvarligt på att monitorera prestandan på din SQL Server rekommenderar jag att du kontinuerligt samlar in data på nyckel-räknarna, och sen gör en trendanalys (kan göras i Excel) för att hitta trender i serverns beteende. Genom detta förfarande kan du förekomma serverns behov för flera CPUer, snabbare I/O eller mer minne. Trendanalys låter dig projicera historisk data i framtiden, vilket ger ett bra underlag att visa upp för din chef när du skall motivera uppgraderingar av din nuvarande hårdvara, eller byta ut hårdvara i dina servrar.
System Monitor är ett kraftfullt verktyg, och du kommer att behöva ta dig tid att lära dig behärska det. Du kommer att finna det mycket praktiskt för att hitta och identifiera prestandaproblem och att hjälpa dig beräkna framtida behov för hårdvara.
0 Kommentarer