Använd SET STATISTICS IO/TIME
Förord
Det här är inte en artikel om optimering av frågor (det ämnet skulle fylla en hel bok), utan en artikel om hur man använder det ofta förbisedda Transact-SQL SET STATISTICS IO och SET STATISTICS TIME kommandot för att hjälpa oss optimera våra SQL-frågor.
Till att börja med, målet med att prestandaoptimera frågor verkar nog så enkelt. I huvudsak vill vi att våra frågor ska köra snabbare. Om vi antingen optimerar en fråga som tar 10 minuter ner till 1 minut, eller en med 2 sekunders frågetid ner till 1 sekund, så är vårt slutliga mål att reducera hur lång tid det tar att köra våra frågor
Med den enkla målbeskrivningen, varför är det då så svårt att åstadkomma det? Eftersom det finns flera skäl till varför frågeoptimering är svårt, fokuserar denna artikel på ett av dessa. Och det är att frågeoptimering ofta sker på instabila miljöer som sekundsnabbt ändrar prestanda, vilket gör det svårt att riktigt veta vad som händer.
Använd SET STATISTICS IO och SET STATISTICS TIME
till din hjälp vid optimering av SQL Server frågor
av Brad M. McGehee
Vad menar jag då med detta? Om du likt de flesta människor som försöker prestandaoptimera dina frågor, antingen utför du ditt jobb på en testserver med testdata eller en kopia på produktionsdata, eller en produktionsserver med produktionsdata. I vilket fall som helst, ingendera server är någonsin exakt från en testkörning till en annan. Kom ihåg att SQL Server justerar sig dynamiskt efter kraven som ställs på dess resurser, krav som ständigt förändras. Eftersom frågeoptimering på en testserver sannerligen kommer att bli mindre dynamisk än på en produktionsserver, kommer praktiskt taget vilken SQL Server som helst att visa liknande problem
Om du inte tror det, kör samma fråga om och om igen på en lastad server, och i de flesta fall, så kommer tiden för de olika exekveringarna att variera. Förmodligen inte alltid, men en hel del, och den kommer att ändra sig på pass mycket att det gör frågeoptimering mer svårt än det borde vara. Ett exempel: Om du frågeoptimerar på en produktionsserver, lägger du märke till att din originalversion kör på ungefär 10 sekunder. Du granskar frågan och märker att du med en liten förändring borde snabba upp frågan. När du nu kör din förändrade och optimerade fråga så tar det 12 sekunder.
Vad har hänt? Var din idé felaktig, eller var möjligen servern tyngre lastad än normalt varje gång du körde din fråga? Du kan ju köra din fråga om och om igen för att få en genomsnittlig tid, det är ju en del jobb. Vad du behöver är ett mer vetenskaplig sätt att jämföra prestandan på en fråga mot en annan.
När du exekverar en fråga på en SQL Server används olika serverresurser. En av dessa är en specifik mängd CPU tid. Om du kör samma fråga om och om igen, förutsatt att databasen inte har förändrats – kommer CPU resurserna som behövs för att köra frågan förbli nästan identiska från exekvering till exekvering (vanligen mindre än 500 millisekunder eller mindre variation mellan körningarna). Jag pratar inte om mängden tid det tar att köra en fråga från början till slut, utan om mängden CPU resurser varje fråga behöver för att exekvera. Mängden tid som det tar att köra en fråga färdigt kommer att variera beroende på hur upptagen servern är.
En annan resurs som SQL Server behöver är IO. När du kör en fråga måste SQL Server returnera data från data cachen (logisk läsning), och om datan den behöver inte finns i cachen måste den läsa den från disk(fysisk läsning)
Det som måste vara uppenbart från den här diskussionen är att ju mer CPU och IO resurser en fråga kräver för att köra, ju långsammare kommer den att prestera. Ett annat sätt att beskriva uppgiften med att optimera frågor är att du vill skriva om dina frågor på ett sätt som gör att de kommer att använda mindre CPU och IO resurser. Om du lyckas med detta, då har du förbättrat prestandan på dina frågor.
Om du vid frågeoptimering tanker i termerna av att reducera de serverresurer som används, (snarare än i termer av hur lång tid det tar för frågan att köra) är det lättare att mäta om de åtgärder du vidtagit för att optimera din fråga verkligen hjälpte eller rentav var en försämring, speciellt på en hårt lastad server vars systemresurser ständigt varierar. Låt oss titta på hur vi kan mäta dessa resurser så att vi kan se om våra frågor använder mer eller mindre resurser när vi optimerar dem.
Både SET STATISTICS IO och SET STATISTICS TIME Transact-SQL kommandona har funnits en längre tid. Men av olika anledningar, är dessa kommandon ofta förbisedda av många DBA när det är fråga om att optimera prestanda på frågor. Kanske för att de inte är så ”sexiga”. Vad än skälen är, är kommandona fortfarande praktiska, som vi kommer att se.
Båda kommandona kan ”slås på” med Transact-SQL kommandon genom Query Analyzer (7.0 och 2000) eller genom inställningar på aktuell anslutnings egenskaper i Query Analyzer. I den här artikeln kommer vi att använda Transact-SQL kommandon som illustration.
SET STATISTICS IO och SET STATISTICS TIME kommandona fungerar som inställningar som slår av och på varierande rapporterad information på de resurser våra frågor använder. Som grundinställning är dessa inställningar inte påslagna. Låt oss då se på ett exempel på hur vi slår på dessa kommandon och vilken information de tillhandahåller.
För att börja vårat exempel, ta upp Query Analyzer och anslut till SQL Server. Till det här exemplet använder vi den ständig återkommande favoriten, Northwind. Kör vidare och gör denna databas till grunddatabas för den här anslutningen.
Nästa, kör denna fråga:
Förutsatt att du inte har förändrat den här tabellen kommer 2 155 rader att returneras. Det här är ditt typiska resultat som du har sett många gånger i Query Analyzer.
Låt oss nu köra samma fråga, men den här gången kör vi SET STATISTICS IO och SET STATISTICS TIME innan vi kör frågan. Kom ihåg att dessa två kommandon ”slår på” prestandamätning bara för den existerande databasanslutningen. Om du kör en eller bägge av dessa kommandon, bryter anslutningen och startar en ny, då måste du upprepa kommandot för den nya anslutningen. Om du någon gång skulle behöva ”slå av” dessa kommandon under pågående anslutning, kan du ersätta OFF med ON i kommandot.
Innan vi börjar med vårat exempel kör vi dessa två kommandon(kör inte dessa på en produktionsserver) först. Dessa två kommandon rensar SQL Server data och procedur cache. Detta hjälper oss att försäkra oss om att vi startar från rätt nivå. Om du inte gör detta varje gång innan du kör följande exempel kommer dina resultat ej att vara konsekventa.
Nästa, skriv och kör följande Transact-SQL commando:
När du gjort det, kör då det här kommandot igen:
(Om du kör alla ovanstående kommandon samtidigt kommer ditt resultat att se annorlunda ut i jämförelse med mitt, och det kommer att bli svårare att se vad som händer. )
När du kört dessa kommandon kommer du att se ny information i resultatfönstret som du inte sett tidigare. Högst upp i fönstret kommer du att se någonting i stil med:
SQL Server parse and compile time:
CPU time = 10 ms, elapsed time = 61 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Efter ovanstående data, kommer raderna från frågan att visas. I slutet av de 2 155 raderna kommer den här tilläggsinformationen att visas:
Table 'Order Details'. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.
SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 387 ms.
(Resultatet kommer hursomhelst att variera, men det förklarar vi nedan när vi förklarar allt du ser här ovanför.)
Vad betyder då allt detta?
SET STATISTICS TIME kommandot används för att mäta en mängd tider, somliga kommer och andra kommer inte att vara av intresse när du prestanda optimerar dina frågor. Det här kommandot ansvarar för följande information som visas på din skärm:
Från toppen av resultatet:
I toppen av resultatet ser du vad som förefaller vara samma tidmätning två gånger, men den första kommer antagligen att ha CPU och förbrukad tid, och den andra antagligen inte. Vad säger då detta oss?
"SQL Server parse and compile time" berättar hur mycket CPU tid och den totala tiden det tog för SQL Server att analysera och kompilera Transact-SQL uttrycket, “SELECT * FROM [order details]”, och sen stoppas frågeplanen som skapades, in i SQL Servers procedur cache för att användas av SQL Server.
I mitt fall tog det 10ms CPU tid och totalt 61ms av förbrukad tid. Din CPU tid kommer förmodligen att variera pga. att din server är annorlunda jämfört med min, och din förbrukade tid kommer att variera därför att både din server och din servers last kommer att vara olik min.
Den andra gången du ser “SQL Server parse and compile time,” anger den mängden tid det tog för SQL-Server att återfå planen från procedur cachen för att den kan exekveras. Generellt kommer tiden här att var 0 beroende på hur snabb frågan är.
Om du skulle köra transact-SQL kommandot “SELECT * FROM [order details]” igen utan att rensa cachen (som rekommenderas ovan), skulle sannolikt båda instanserna av CPU tid och kompileringstid bli 0, eftersom den cachade planen skulle återanvändas så att ingen ny kompileringstid skulle behövas.
Hur är då denna information användbar vid prestanda optimering? Ja, inte så mycket men jag behövde få förklara vad den här informationen betydde. Du kommer att bli överraskad att de flesta DBA inte vet vad de betyder.
Vad är då mest intressant för oss av den tidsinformation som visades i slutet av frågeresultatet, här kommer det igen:
SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 387 ms.
Vad det här säger är hur mycket CPU tid som användes för att exekvera frågan, och hur lång tid frågan tog att köra. Av dessa två informationsbitar, är bara den första till stor nytta. CPU tiden är en relativt överrensstämmande mätning av mängden CPU resurser det tar för en fråga att köras, och den är relativt oberoende av hur upptagen din CPU är. Du kommer säkert att se lite variationer i den här siffran, men det kommer inte att vara betydande. Den förflutna tiden är ett mått på hur lång tid frågan tog att exekvera (räknar ej tiden för låsningar och läsningar). Den här siffran kommer att hoppa runt lite grann beroende på den ständigt varierande lasten på din server.
CPU tiden är relativt konsekvent, du kan använda den som ett sätt att fastsälla huruvida ändringen du gjorde i din fråga under prestandaoptimeringen, verkligen hjälpte eller rentav var en försämring. Mer om detta senare.
SET STATISTICS IO Results
SET STATISTICS IO information visas i botten av resultatet. Här är den igen:
Table 'Order Details'. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.
En del av den här informationen är väldigt användbar, annan inte. Vi tittar på dessa och lär oss vad de betyder.
Scan Count: Refererar till det antal gånger som tabellen refererad i frågan har blivit accessad. I vårat exempel, var tabellen i våran fråga endast acessad 1 gång. För frågor som inte inkluderar sammanslagningar (joins) är inte den här informationen så hjälpfull. Men om din fråga har en eller flera sammanslagningar, då kan den här informationen vara användbar för dig.
Ett exempel: En yttre tabell i en nästlad loop skulle ha en Scan Count på 1. Men för en inre tabell, refererar Scan Count till det antal gånger tabellen accessats under en nästlad loop. Du kan ju föreställa dig att ju mindre Scan Count som används för den inre tabellen, ju mindre resurser använder den och ju snabbare genomförs din fråga. När du optimerar frågor med sammanslagningar ska du lägga märke till att Scan Count för att se om den ökar eller minskar allt eftersom du optimerar frågan.
Logical Reads: Detta är det mest användningsfulla data som SET STATISTICS IO eller SET STATISTICS TIME kommandot tillhandahåller. Som du förmodligen vet, innan SQL Server kan göra någonting med data så måste det datat vara i data cachen. Som tillägg och som du förmodligen vet så läser SQL Server data från data cachen, och då i sidor om 8K.
Vad är då en logisk läsning? En logisk läsning är det antal sidor som SQL Server behövde läsa från data cachen när den producerar resultatet som frågan din krävde. SQL Server läser varken mer eller mindre data sidor än den behöver för att exekvera en fråga. Pga. detta, kommer exakt samma antal logiska läsningar att förekomma när du kör samma fråga mot exakt samma data mot SQL Servern.
Varför är det då så viktigt vid frågeoptimering att hålla reda på de logiska läsningar som SQL Servern utför? Jo, därför att det är en av de saker som aldrig ändrar sig från en exekvering till en annan på samma fråga. Och pga detta, är det ett perfekt mått att använda för att se om dina optimeringar lyckas eller inte.
När du optimerar dina frågor och antalet logiska läsningar minskar, då vet du att din fråga använder mindre serverresurser och resulterar i ökad prestanda. Går däremot antalet logiska läsningar upp, vad än du gjort för att optimera dina frågor, så skadar din fråga prestandan, Ju färre logiska läsningar som utförs i en fråga, ju effektivare är den, och desto snabbare kommer den att prestera, förutsatt att allt annat är oförändrat.
Fysiska läsningar (Physical Reads): Det jag kommer att skriva om här är förmodligen en aning förvirrande, men det borde ändå komma till klarhet när du tänker på det. Först av allt, en fysisk läsning hänför sig till när SQL Server måste läsa de datasidor den behöver från disk för att lägga dessa i data cachen innan den kan exekvera frågan. Som jag menade ovan, så måste all data vara i datacachen inan frågan kan exekvera. Det första SQL Servern gör innan den börjar köra frågan är att kolla om datat finns i cachen. Om de finns där, toppen. Men om de inte finns där, då måste SQL Servern först gå ner på disk och läsa upp sidorna i datacachen.
Du kan ju tänka dig att det tar en hel del extra serveresurser för SQL Server att köra en fysisk läsning i jämförelse med en logisk läsning, och för bästa prestanda skall vi undvika fysiska läsningar när vi kan.
Som du säkert kan föreställa dig, så tar det en hel del extra resurser från SQL Servern att utföra en fysisk läsning i jämförelse med en logisk läsning
Här kommer en del som är förvirrande. Du ska ignorera fysiska läsningar när du optimerar dina frågor, fokusera istället bara på logiska läsningar. Hur kan det då bli så, jag sa ju just att fysiska läsningar var mycket mer resurskrävande än logiska läsningar?
Det är en sann förklaring, men antalet fysiska läsningar som SQL Server använder för att exekvera en fråga kan inte reduceras genom frågeoptimering. Att reducera fysiska läsningar är en viktig uppgift för DBA:n, men där är det fokus på att optimera hela servern, inte frågeoptimering. När du optimerar frågor har du ingen kontroll på storleken av data cachen eller hur hårt lastad servern är, eller huruvida datat som din fråga kräver ligger på disk eller i cachen. Det enda du direkt kan kontrollera är det antal logiska läsningar som krävs för att din fråga skall generera det resultat du behöver.
Pga. detta kan du tryggt ignorera den fysiska läsningen som tillhandahålls av kommandot SET STATISTICS IO (Not: Ett sätt att reducera fysiska läsningar och snabba upp din SQL Server, är att försäkra sig om att din server har massor av fysikt RAM)
Read-Ahead Reads: Som fysiska läsningar, detta är inte användbart vid frågeoptimering. Det här värdet ger det antal fysiska läsningar som SQL Server genomför som en del i dess read-ahead mekanism. För att hjälpa optimera dess prestanda, så läser SQL Server fysiska data sidor i förväg när den tror att dina frågor kan komma att behöva datat. De sidor som är read-ahead kan komma till användning eller ej, baserat på hur väl SQL Server gissad vilka dina behov var.
I mitt exempel noterade vi att read-ahead sidorna var 9, att de fysiska läsningarna var 1, och att de logiska läsningarna var 10. Går dessa siffror ihop? Ja det gör dom. Här är vad som hände när jag körde frågan på min server. Först av allt, SQL Server startade med att kolla om de data sidor den behövde för att fullfölja frågan fanns i data cachen. I ungefär samma tidpunkt så var den smart nog att lista ut att så inte var fallet, och då sparkade read-ahead mekanismen igång och läste ut de första 9 sidorna av de 10 den behövde från disk och placerade dem i cachen. När SQL Servern kollade om alla 10 sidor fanns i cachen så fann den att 9 st redan fanns där (pga av read-aheadmekanismen), men en saknade. Pga. detta så behövde SQL Servern endast hämta upp den sista biten och putta in den i cachen. När då allt data låg i cachen var SQL Server redo att köra frågan.
SQL Server är smartare än du trodde, eller hur?
Som jag sa i början av artikeln, så är det viktigt vid prestandaoptimering att du har någon vetenskapligt metodik för att fastställa om dina optimeringsansträngningar ger resultat. Problemet är ju som du kommer ihåg att SQL Server ofta är dynamisk, och att då använda sig av totala frågetiden som dit enda mätinstrument om den fråga du optimerar går snabbare eller långsammare, är inte ett schysst sätt att mäta dina uppoffringar.
Ett bättre sätt är då att jämföra mer vetenskapliga siffror, som antalet logiska läsningar eller CPU tiden det tar att köra din fråga. När du ska prestandaoptimera dina frågor så kanske du vill börja använda SET STATISTICS IO och SET STATISTICS TIME kommandona för att säkerställa att du får pålitligt data till din hjälp för att exakt veta hur framgångsrik din frågeoptimering verkligen varit.
till din hjälp vid optimering av SQL Server frågor
av Brad M. McGehee
Vad menar jag då med detta? Om du likt de flesta människor som försöker prestandaoptimera dina frågor, antingen utför du ditt jobb på en testserver med testdata eller en kopia på produktionsdata, eller en produktionsserver med produktionsdata. I vilket fall som helst, ingendera server är någonsin exakt från en testkörning till en annan. Kom ihåg att SQL Server justerar sig dynamiskt efter kraven som ställs på dess resurser, krav som ständigt förändras. Eftersom frågeoptimering på en testserver sannerligen kommer att bli mindre dynamisk än på en produktionsserver, kommer praktiskt taget vilken SQL Server som helst att visa liknande problem
Om du inte tror det, kör samma fråga om och om igen på en lastad server, och i de flesta fall, så kommer tiden för de olika exekveringarna att variera. Förmodligen inte alltid, men en hel del, och den kommer att ändra sig på pass mycket att det gör frågeoptimering mer svårt än det borde vara. Ett exempel: Om du frågeoptimerar på en produktionsserver, lägger du märke till att din originalversion kör på ungefär 10 sekunder. Du granskar frågan och märker att du med en liten förändring borde snabba upp frågan. När du nu kör din förändrade och optimerade fråga så tar det 12 sekunder.
Vad har hänt? Var din idé felaktig, eller var möjligen servern tyngre lastad än normalt varje gång du körde din fråga? Du kan ju köra din fråga om och om igen för att få en genomsnittlig tid, det är ju en del jobb. Vad du behöver är ett mer vetenskaplig sätt att jämföra prestandan på en fråga mot en annan.
Att mäta SQL Server resurser är nyckeln till att förenkla frågeoptimeringsproblem
När du exekverar en fråga på en SQL Server används olika serverresurser. En av dessa är en specifik mängd CPU tid. Om du kör samma fråga om och om igen, förutsatt att databasen inte har förändrats – kommer CPU resurserna som behövs för att köra frågan förbli nästan identiska från exekvering till exekvering (vanligen mindre än 500 millisekunder eller mindre variation mellan körningarna). Jag pratar inte om mängden tid det tar att köra en fråga från början till slut, utan om mängden CPU resurser varje fråga behöver för att exekvera. Mängden tid som det tar att köra en fråga färdigt kommer att variera beroende på hur upptagen servern är.
En annan resurs som SQL Server behöver är IO. När du kör en fråga måste SQL Server returnera data från data cachen (logisk läsning), och om datan den behöver inte finns i cachen måste den läsa den från disk(fysisk läsning)
Det som måste vara uppenbart från den här diskussionen är att ju mer CPU och IO resurser en fråga kräver för att köra, ju långsammare kommer den att prestera. Ett annat sätt att beskriva uppgiften med att optimera frågor är att du vill skriva om dina frågor på ett sätt som gör att de kommer att använda mindre CPU och IO resurser. Om du lyckas med detta, då har du förbättrat prestandan på dina frågor.
Om du vid frågeoptimering tanker i termerna av att reducera de serverresurer som används, (snarare än i termer av hur lång tid det tar för frågan att köra) är det lättare att mäta om de åtgärder du vidtagit för att optimera din fråga verkligen hjälpte eller rentav var en försämring, speciellt på en hårt lastad server vars systemresurser ständigt varierar. Låt oss titta på hur vi kan mäta dessa resurser så att vi kan se om våra frågor använder mer eller mindre resurser när vi optimerar dem.
SET STATISTICS IO och SET STATISTICS TIME - Räddningen
Både SET STATISTICS IO och SET STATISTICS TIME Transact-SQL kommandona har funnits en längre tid. Men av olika anledningar, är dessa kommandon ofta förbisedda av många DBA när det är fråga om att optimera prestanda på frågor. Kanske för att de inte är så ”sexiga”. Vad än skälen är, är kommandona fortfarande praktiska, som vi kommer att se.Båda kommandona kan ”slås på” med Transact-SQL kommandon genom Query Analyzer (7.0 och 2000) eller genom inställningar på aktuell anslutnings egenskaper i Query Analyzer. I den här artikeln kommer vi att använda Transact-SQL kommandon som illustration.
SET STATISTICS IO och SET STATISTICS TIME kommandona fungerar som inställningar som slår av och på varierande rapporterad information på de resurser våra frågor använder. Som grundinställning är dessa inställningar inte påslagna. Låt oss då se på ett exempel på hur vi slår på dessa kommandon och vilken information de tillhandahåller.
För att börja vårat exempel, ta upp Query Analyzer och anslut till SQL Server. Till det här exemplet använder vi den ständig återkommande favoriten, Northwind. Kör vidare och gör denna databas till grunddatabas för den här anslutningen.
Nästa, kör denna fråga:
SELECT * FROM [order details]
Förutsatt att du inte har förändrat den här tabellen kommer 2 155 rader att returneras. Det här är ditt typiska resultat som du har sett många gånger i Query Analyzer.
Låt oss nu köra samma fråga, men den här gången kör vi SET STATISTICS IO och SET STATISTICS TIME innan vi kör frågan. Kom ihåg att dessa två kommandon ”slår på” prestandamätning bara för den existerande databasanslutningen. Om du kör en eller bägge av dessa kommandon, bryter anslutningen och startar en ny, då måste du upprepa kommandot för den nya anslutningen. Om du någon gång skulle behöva ”slå av” dessa kommandon under pågående anslutning, kan du ersätta OFF med ON i kommandot.
Innan vi börjar med vårat exempel kör vi dessa två kommandon(kör inte dessa på en produktionsserver) först. Dessa två kommandon rensar SQL Server data och procedur cache. Detta hjälper oss att försäkra oss om att vi startar från rätt nivå. Om du inte gör detta varje gång innan du kör följande exempel kommer dina resultat ej att vara konsekventa.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Nästa, skriv och kör följande Transact-SQL commando:
SET STATISTICS IO ON
SET STATISTICS TIME ON
När du gjort det, kör då det här kommandot igen:
SELECT * FROM [order details]
(Om du kör alla ovanstående kommandon samtidigt kommer ditt resultat att se annorlunda ut i jämförelse med mitt, och det kommer att bli svårare att se vad som händer. )
När du kört dessa kommandon kommer du att se ny information i resultatfönstret som du inte sett tidigare. Högst upp i fönstret kommer du att se någonting i stil med:
SQL Server parse and compile time:
CPU time = 10 ms, elapsed time = 61 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Efter ovanstående data, kommer raderna från frågan att visas. I slutet av de 2 155 raderna kommer den här tilläggsinformationen att visas:
Table 'Order Details'. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.
SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 387 ms.
(Resultatet kommer hursomhelst att variera, men det förklarar vi nedan när vi förklarar allt du ser här ovanför.)
Vad betyder då allt detta?
SET STATISTICS TIME Resultat
SET STATISTICS TIME kommandot används för att mäta en mängd tider, somliga kommer och andra kommer inte att vara av intresse när du prestanda optimerar dina frågor. Det här kommandot ansvarar för följande information som visas på din skärm:Från toppen av resultatet:
SQL Server parse and compile time:
CPU time = 10 ms, elapsed time = 61 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Från slutet av resultatet:
SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 387 ms.
I toppen av resultatet ser du vad som förefaller vara samma tidmätning två gånger, men den första kommer antagligen att ha CPU och förbrukad tid, och den andra antagligen inte. Vad säger då detta oss?
"SQL Server parse and compile time" berättar hur mycket CPU tid och den totala tiden det tog för SQL Server att analysera och kompilera Transact-SQL uttrycket, “SELECT * FROM [order details]”, och sen stoppas frågeplanen som skapades, in i SQL Servers procedur cache för att användas av SQL Server.
I mitt fall tog det 10ms CPU tid och totalt 61ms av förbrukad tid. Din CPU tid kommer förmodligen att variera pga. att din server är annorlunda jämfört med min, och din förbrukade tid kommer att variera därför att både din server och din servers last kommer att vara olik min.
Den andra gången du ser “SQL Server parse and compile time,” anger den mängden tid det tog för SQL-Server att återfå planen från procedur cachen för att den kan exekveras. Generellt kommer tiden här att var 0 beroende på hur snabb frågan är.
Om du skulle köra transact-SQL kommandot “SELECT * FROM [order details]” igen utan att rensa cachen (som rekommenderas ovan), skulle sannolikt båda instanserna av CPU tid och kompileringstid bli 0, eftersom den cachade planen skulle återanvändas så att ingen ny kompileringstid skulle behövas.
Hur är då denna information användbar vid prestanda optimering? Ja, inte så mycket men jag behövde få förklara vad den här informationen betydde. Du kommer att bli överraskad att de flesta DBA inte vet vad de betyder.
Vad är då mest intressant för oss av den tidsinformation som visades i slutet av frågeresultatet, här kommer det igen:
SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 387 ms.
Vad det här säger är hur mycket CPU tid som användes för att exekvera frågan, och hur lång tid frågan tog att köra. Av dessa två informationsbitar, är bara den första till stor nytta. CPU tiden är en relativt överrensstämmande mätning av mängden CPU resurser det tar för en fråga att köras, och den är relativt oberoende av hur upptagen din CPU är. Du kommer säkert att se lite variationer i den här siffran, men det kommer inte att vara betydande. Den förflutna tiden är ett mått på hur lång tid frågan tog att exekvera (räknar ej tiden för låsningar och läsningar). Den här siffran kommer att hoppa runt lite grann beroende på den ständigt varierande lasten på din server.
CPU tiden är relativt konsekvent, du kan använda den som ett sätt att fastsälla huruvida ändringen du gjorde i din fråga under prestandaoptimeringen, verkligen hjälpte eller rentav var en försämring. Mer om detta senare.
SET STATISTICS IO Results
SET STATISTICS IO information visas i botten av resultatet. Här är den igen:
Table 'Order Details'. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.
En del av den här informationen är väldigt användbar, annan inte. Vi tittar på dessa och lär oss vad de betyder.
Scan Count: Refererar till det antal gånger som tabellen refererad i frågan har blivit accessad. I vårat exempel, var tabellen i våran fråga endast acessad 1 gång. För frågor som inte inkluderar sammanslagningar (joins) är inte den här informationen så hjälpfull. Men om din fråga har en eller flera sammanslagningar, då kan den här informationen vara användbar för dig.
Ett exempel: En yttre tabell i en nästlad loop skulle ha en Scan Count på 1. Men för en inre tabell, refererar Scan Count till det antal gånger tabellen accessats under en nästlad loop. Du kan ju föreställa dig att ju mindre Scan Count som används för den inre tabellen, ju mindre resurser använder den och ju snabbare genomförs din fråga. När du optimerar frågor med sammanslagningar ska du lägga märke till att Scan Count för att se om den ökar eller minskar allt eftersom du optimerar frågan.
Logical Reads: Detta är det mest användningsfulla data som SET STATISTICS IO eller SET STATISTICS TIME kommandot tillhandahåller. Som du förmodligen vet, innan SQL Server kan göra någonting med data så måste det datat vara i data cachen. Som tillägg och som du förmodligen vet så läser SQL Server data från data cachen, och då i sidor om 8K.
Vad är då en logisk läsning? En logisk läsning är det antal sidor som SQL Server behövde läsa från data cachen när den producerar resultatet som frågan din krävde. SQL Server läser varken mer eller mindre data sidor än den behöver för att exekvera en fråga. Pga. detta, kommer exakt samma antal logiska läsningar att förekomma när du kör samma fråga mot exakt samma data mot SQL Servern.
Varför är det då så viktigt vid frågeoptimering att hålla reda på de logiska läsningar som SQL Servern utför? Jo, därför att det är en av de saker som aldrig ändrar sig från en exekvering till en annan på samma fråga. Och pga detta, är det ett perfekt mått att använda för att se om dina optimeringar lyckas eller inte.
När du optimerar dina frågor och antalet logiska läsningar minskar, då vet du att din fråga använder mindre serverresurser och resulterar i ökad prestanda. Går däremot antalet logiska läsningar upp, vad än du gjort för att optimera dina frågor, så skadar din fråga prestandan, Ju färre logiska läsningar som utförs i en fråga, ju effektivare är den, och desto snabbare kommer den att prestera, förutsatt att allt annat är oförändrat.
Fysiska läsningar (Physical Reads): Det jag kommer att skriva om här är förmodligen en aning förvirrande, men det borde ändå komma till klarhet när du tänker på det. Först av allt, en fysisk läsning hänför sig till när SQL Server måste läsa de datasidor den behöver från disk för att lägga dessa i data cachen innan den kan exekvera frågan. Som jag menade ovan, så måste all data vara i datacachen inan frågan kan exekvera. Det första SQL Servern gör innan den börjar köra frågan är att kolla om datat finns i cachen. Om de finns där, toppen. Men om de inte finns där, då måste SQL Servern först gå ner på disk och läsa upp sidorna i datacachen.
Du kan ju tänka dig att det tar en hel del extra serveresurser för SQL Server att köra en fysisk läsning i jämförelse med en logisk läsning, och för bästa prestanda skall vi undvika fysiska läsningar när vi kan.
Som du säkert kan föreställa dig, så tar det en hel del extra resurser från SQL Servern att utföra en fysisk läsning i jämförelse med en logisk läsning
Här kommer en del som är förvirrande. Du ska ignorera fysiska läsningar när du optimerar dina frågor, fokusera istället bara på logiska läsningar. Hur kan det då bli så, jag sa ju just att fysiska läsningar var mycket mer resurskrävande än logiska läsningar?
Det är en sann förklaring, men antalet fysiska läsningar som SQL Server använder för att exekvera en fråga kan inte reduceras genom frågeoptimering. Att reducera fysiska läsningar är en viktig uppgift för DBA:n, men där är det fokus på att optimera hela servern, inte frågeoptimering. När du optimerar frågor har du ingen kontroll på storleken av data cachen eller hur hårt lastad servern är, eller huruvida datat som din fråga kräver ligger på disk eller i cachen. Det enda du direkt kan kontrollera är det antal logiska läsningar som krävs för att din fråga skall generera det resultat du behöver.
Pga. detta kan du tryggt ignorera den fysiska läsningen som tillhandahålls av kommandot SET STATISTICS IO (Not: Ett sätt att reducera fysiska läsningar och snabba upp din SQL Server, är att försäkra sig om att din server har massor av fysikt RAM)
Read-Ahead Reads: Som fysiska läsningar, detta är inte användbart vid frågeoptimering. Det här värdet ger det antal fysiska läsningar som SQL Server genomför som en del i dess read-ahead mekanism. För att hjälpa optimera dess prestanda, så läser SQL Server fysiska data sidor i förväg när den tror att dina frågor kan komma att behöva datat. De sidor som är read-ahead kan komma till användning eller ej, baserat på hur väl SQL Server gissad vilka dina behov var.
I mitt exempel noterade vi att read-ahead sidorna var 9, att de fysiska läsningarna var 1, och att de logiska läsningarna var 10. Går dessa siffror ihop? Ja det gör dom. Här är vad som hände när jag körde frågan på min server. Först av allt, SQL Server startade med att kolla om de data sidor den behövde för att fullfölja frågan fanns i data cachen. I ungefär samma tidpunkt så var den smart nog att lista ut att så inte var fallet, och då sparkade read-ahead mekanismen igång och läste ut de första 9 sidorna av de 10 den behövde från disk och placerade dem i cachen. När SQL Servern kollade om alla 10 sidor fanns i cachen så fann den att 9 st redan fanns där (pga av read-aheadmekanismen), men en saknade. Pga. detta så behövde SQL Servern endast hämta upp den sista biten och putta in den i cachen. När då allt data låg i cachen var SQL Server redo att köra frågan.
SQL Server är smartare än du trodde, eller hur?
Vad betyder då allt detta för dig??
Som jag sa i början av artikeln, så är det viktigt vid prestandaoptimering att du har någon vetenskapligt metodik för att fastställa om dina optimeringsansträngningar ger resultat. Problemet är ju som du kommer ihåg att SQL Server ofta är dynamisk, och att då använda sig av totala frågetiden som dit enda mätinstrument om den fråga du optimerar går snabbare eller långsammare, är inte ett schysst sätt att mäta dina uppoffringar.
Ett bättre sätt är då att jämföra mer vetenskapliga siffror, som antalet logiska läsningar eller CPU tiden det tar att köra din fråga. När du ska prestandaoptimera dina frågor så kanske du vill börja använda SET STATISTICS IO och SET STATISTICS TIME kommandona för att säkerställa att du får pålitligt data till din hjälp för att exakt veta hur framgångsrik din frågeoptimering verkligen varit.
0 Kommentarer