Verktyg för att mäta prestanda i SQL Server
Förord
Denna artikel beskriver några metoder man kan använda för att mäta prestandan i de SQL-satser och procedurer man skriver i SQL Server. Artikeln finns även publicerad på Hedgate.NET tillsammans med andra SQL Server-relaterade artiklar.Innehåll
»»
»
»
»
»
Optimera, optimera, optimera
Problem och uppgifter man ska lösa i SQL går nästan alltid att lösa på flera olika sätt. Är du som jag intresserad av att skriva optimerad SQL kod, som tar så kort tid som möjligt på sig att exekvera samt utnyttjar så lite resurser som möjligt, så provar du säkert flera alternativ innan du beslutar dig för ett att använda. Men för att kunna besluta vilket alternativ som är bäst behöver man något sätt att mäta, och det är vad jag ska gå genom här.
Klockan
Det enklaste sättet att mäta prestanda, men naturligtvis inte det mest exakta, är förstås att titta på klockan och se hur lång tid som går från det att du startar exekveringen av din SQL-fråga (batch, procedur el dyl) till dess att exekveringen är klar. SQL Query Analyzer har en liten tidmätare i statusraden som visar timmar:minuter:sekunder. Dock är detta inte ett särskilt exakt mätinstrument. Många frågor du vill mäta tar inte över en sekund att exekvera, de flesta bör faktiskt inte ta mer än några tiotals millisekunder om de optimerats ordentligt. Alltså är inte klockan i QA ett särskilt bra verktyg att mäta prestandan med.Ett bättre sätt att mäta hur lång tid som passerar under en exekvering är att använda den inbyggda funktionen GETDATE(). Exemplet nedan visar hur detta kan gå till (WAITFOR som används i exemplet gör inget annat än att vänta så länge som anges i DELAY). Genom att först lagra datum/tiden när exekveringen börjar och sedan jämföra denna mot tidpunkten när exekveringen är klar, så får man en mycket exaktare mätning i millisekunder (om man så önskar). Man ska dock vara medveten om att noggranheten bara ligger på 1/300 sekund (alltså 3,33 ms), det vill säga att om något tar 40 ms så betyder det ungefär 40-43 ms. men detta räcker normalt. Man ska dock tänka på att det är inte alltid tiden som är det intressanta, även hur mycket systemresurser som används kan vara viktigt.
-- Exempel 1
DECLARE @start datetime, @stop datetime
SET @start = GETDATE()
WAITFOR DELAY '00:00:00.080' -- vänta i 80 ms
SET @stop = GETDATE()
SELECT 'Exekveringen tog ' + CONVERT(varchar(10), DATEDIFF(ms, @start, @stop)) + ' ms'
STATISTICS TIME
Ett annat alternativ för att mäta tid är att aktivera konfigurationsparametern STATISTICS TIME. Syntaxen för detta är följande: SET STATISTICS TIME {ON | OFF}När denna parameter är aktiv så visas tidsstatistik i resultatfönstret i QA (om man visar ressultaten i diagramform istället för textmode som jag normalt använder så skrivs det ut i Messages-fliken). Exemplet nedan visar hur det kan se ut.
-- Exempel 2
USE Northwind
GO
SET STATISTICS TIME ON
SELECT * FROM orders
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(rader som blev resultatet på frågan)
SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 500 ms.
Detta kan vid första anblicken se komplicerat ut, men i princip är det bara att leta upp den raden med Execution Time som skrevs ut direkt efter resultatet på frågan. Högre upp kan man se tiden det tog för frågeoptimeraren att gå genom och kompilera frågan, men den tiden är inte så intressant eftersom den oftast är 0 ms om man kör frågan flera gånger. Vad som är intressant är alltså hur lång tid det tog för frågan att exekveras. I exemplet ovan tog det 30 ms CPU-tid, men den sammanlagda tiden som gick åt var 500 ms (pröva byta ut WAITFOR i exempel 1 mot SELECT-satsen och se om GETDATE()-metoden ger samma sammanlagda tid). Var är då de resterande 470 ms kan man fråga sig? Svaret är enkelt: I/O.
STATISTICS IO
I/O är som ni säkert känner till förkortning för input/output. Lite halvfritt översatt till svenska kan man kalla det läsning/skrivning, och man brukar oftast använda det när man pratar om att en applikation läser/skriver från/till något medium, oftast hårddisk eller primärminne, men det gäller även exempelvis nätverkstrafik. För de som inte känner till det så fungerar SQL Server (väldigt enkelt sett) så att för att returnera ett svar på en fråga, ex. rader i en tabell, så måste raderna ligga i primärminnet (RAM). Om de inte redan gör det så måste den först läsa in dem från hårddisken där de ligger lagrade och sedan skriva dem till minnet, för att slutligen hämta dem från minnet till klienten. När de sedan ligger i minnet så sparas de (helt eller delvis) där en tid vilken beroende på ett flertal olika faktorer kan vara allt från 0 - ~ (evighet). Därför kan det ta längre tid att exekvera en fråga första gången den körs (eftersom raderna måste hämtas från disk), så när man mäter hur lång tid en fråga tar bör man alltid köra den ett par gånger för att få ett korrekt resultat (om det inte är en fråga som endast ska köras sporadiskt förstås). Även parsning och kompilering är overhead som man får med i totaltiden om man bara kör frågan en gång, så även det är en anledning till att köra om ett par gånger.Som jag sa tidigare så är inte tiden det tar för en fråga att exekvera det enda som är intressant, utan även hur mycket systemresurser som används är viktigt. Eftersom I/O oftast är det långsammaste i en fråga, framförallt om läsning från disk måste göras vilket man bör räkna med till viss del då det inte alls är säkert att datan ligger i minnet, så är det mycket intressant att veta hur mycket data som måste gås genom för att leverera svaret på frågan. Detta kan vi mäta med hjälp av en annan konfigurationsparameter, STATISTICS IO. Syntaxen liknar den för STATISTICS TIME: SET STATISTICS IO {ON | OFF}
Resultatet är däremot annorlunda. På min dator fick jag följande output (när jag kört ett par gånger):
Table 'Orders'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0.
Först har vi namnet på tabellen. Därefter anges hur många gånger som tabellen scannats för att hämta resultatet. Efter det visas hur många sidor (se Notis 1) som lästs ur datacachen i primärminnet, hur många sidor som lästs från hårddisk samt hur många sidor som placerats i cachen för frågan. De värden som är mest intressanta är logical reads och physical reads samt scan count, och alla ska naturligtvis vara så låga som möjligt. Det kan dock vara bättre att få 100 logical reads än 10 physical reads (eftersom det går snabbare att läsa från RAM än hårddisk), men generellt sett kan vi säga att man ska försöka arbeta ner alla värden så mycket som möjligt.
Notis 1 SQL Server lagrar data i s k datasidor (eng. data pages) som är 8 KB stora. För att hämta en rad (som ex. tar 20 byte lagringsplats) måste hela den sida som raden är lagrad på hämtas upp. Om SQL Server behöver läsa 10 sidor betyder det alltså att den måste läsa in 80 KB. Även index lagras på samma sätt, och siffrorna i STATISTICS IO kan vara både data- och/eller indexsidor. |
Övriga verktyg
Med de metoder som nämnts har man bra möjligheter att besluta vilket alternativ av flera olika SQL-satser man vill använda för att lösa en uppgift optimalt. Det finns ett antal andra verktyg och metoder man kan utnyttja, men de tänker jag ta upp i en senare artikel (eller artiklar). Om ni själva vill experimentera så kan jag rekommendera att ni tittar på följande:* Exekveringsplan - om man trycker Ctrl-K i QA så får man en extra flik när man kör sin SQL-batch. Denna visar en grafisk presentation av den exekveringsplan som optimeraren valt att använda för att exekvera frågan, och i denna kan man finna mycket intressant information.
* SET STATISTICS PROFILE: Detta alternativ ger en profil av exekveringen. På ren svenska innebär det i princip att man får en textbaserad variant av exekveringsplanen, och man kan se vissa intressanta värden i den (ex. hur många rader som returnerats i varje steg).
* SET SHOWPLAN_ALL och SET SHOWPLAN_TEXT: Dessa konfigurationsalternativ innebär bägge att SQL-satsen ej exekveras, utan istället presenteras information om vilka resurser som skulle utnyttjats för att exekvera frågan samt hur det skulle skett.
* Profiler, Performance Monitor och andra externa applikationer: Slutligen finns det ett antal externa (dvs utanför SQL Server) applikationer man kan köra för att mäta olika värden i SQL Server och systemet under körning. Profiler som ingår i SQL Servers 'verktygslåda' kan logga en massa händelser i SQL Server under körning, och Performance Monitor loggar en stor mängd mätvärden både i SQL Server och i systemet.
0 Kommentarer