Hej alla! Nyligen stötte jag på ett problem där det tog lång tid att behandla ett dokument.

Indata: konfiguration "Manufacturing Enterprise Management, utgåva 1.3 (1.3.52.1)", dokument "Inkommande betalningsorder". Klagomål: att hålla i arbetsdatabasen varar 20-30 sekunder, vilket är intressant, i en kopia av databasen hålls samma dokument i 2-4 sekunder. Läs om utredningarna och orsaken till detta beteende nedan.

Alltså med hjälp prestationsmätning Jag tror att alla vet hur man använder det, den skyldige hittades:

I det här fallet spelades en tom uppsättning poster in på brännaren, med andra ord raderades rörelser innan de kördes. Det är värt att notera att denna procedur kallades 26 gånger, dvs. för varje register som vårt dokument kunde skriva till.

Enligt prestationsmätningar tog denna operation 13 sekunder, räknar man ut medelvärdet får man 0,5 sekunder per register, en evighet!

Som vi alla vet kan vi inte optimera inspelningen, men det är helt klart något fel här.
För ytterligare analys, öppna SQL Server Profiler Och . För analys använde jag händelseklasser:

• Showplan Statistik Profil
• Showplan XML-statistikprofil
• RPC slutförd
• SQL: BatchCompleted.

I spårningsinställningarna finns filtrera efter SPID:

SPID är databasserverns process-ID. När det gäller 1C är det i huvudsak en anslutning mellan 1C-servern och DBMS; du kan se den i 1C-servrarnas administrationskonsol i kolumnen "Anslutning till DBMS".

Visas om det här ögonblicket anslutningen till databasen fångas upp av sessionen: antingen görs ett DBMS-anrop eller så är en transaktion öppen, eller så hålls objektet "Temporary Table Manager", där minst en temporär tabell har skapats.

Låt oss skriva en bearbetning för att hålla SPID, den kommer att innehålla en procedur:

Det är viktigt att anslutningsobjektet som hålls, i vårt fall den temporära tabellhanteraren, definieras som en bearbetningsvariabel. Vi öppnar bearbetningen, kör proceduren och så länge den är öppen kommer SPID:en att fixas. Öppna 1C-serverns administrationskonsol:

Så SPID har tagits emot, vi anger dess värde i filtret och får ett spår från den aktuella arbetsdatabasen för vår session. Vid analys av spåret hittades en operation som tog 11 sekunder att slutföra:

Det som också fångade mig var antalet avläsningar - 1872578 , men jag fäste inte direkt någon vikt vid detta och började ta reda på vad som gjordes här och med vilket bord.

exec sp_executesql <= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))’,N’@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)’,0x8A2F00155DBF491211E87F56DD1A416E,’4018-05-31 23:59:59′,0x00000000000000000000000000000000,’4018-05-31 23:59:59′,0x9A95A0369F30F8DB11E46684B4F0A05F,’4018-05-31 23:59:59"

Som du kan se från SQL-frågan bearbetas tabellen "AccRg1465" Detta är en tabell i det självbärande redovisningsregistret. Textuell representation av frågeexekveringsplanen:

Som du kan se från exekveringsplanen för SQL-frågan, händer inget dåligt, tabellen " AccRg1465", klustrad indexsökning används överallt.

Jag såg heller inget fel med den grafiska planen, även om den verkade för uppsvälld för mig - det var en sammanslagning och två kapslade slingor utan någon uppenbar anledning. Var kommer detta antal avläsningar och gigantiska avrättningstider ifrån?

Som nämnts ovan återgavs problemet inte i en ny kopia av databasen, kopian togs från arbetsdatabasen efter att problemet dök upp i den, så det beslutades att analysera dess beteende i SQL Server Profiler på samma dokument.
Här är resultaten:

Frågetext i SQL:

EXEC sp_executesql N"VÄLJ TOP 1 0x01 FRÅN dbo._AccRg1465 T1 WHERE (T1._RecorderTRef = 0x0000022D OCH T1._RecorderRRef = @P1) OCH ((((T1._Period)<= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))" , N"@P1 varbinär(16),@P2 datumtid2(3),@P3 varbinär(16),@P4 datumtid2(3),@P5 varbinär(16),@P6 datumtid2(3)", 0x8A2F00155DBF491211E87F56DD1A416E, "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" , "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" , "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" , "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" , 0x9A95A0F4601F 31-05-08 23:59:59"

Grafisk representation av frågeplanen:

Förfrågningstexterna är desamma, genomförandeplanerna är radikalt olika. Vad kan det vara för fel? Jag hade fel om statistiken i SQL, men den är densamma mellan arbetet och kopian av databasen, och statistiken lagras i databasen för varje tabell:

Låt oss analysera vidare: om statistiken är densamma, men frågeplanerna är olika, betyder det att optimeraren inte kommer åt statistik för att bygga en frågeplan, utan den har en cachad plan som den använder. Vi rensar procedurcachen i vår databas, för detta använder vi kommandot

DBCC FLUSHPROCINDB(< database_id >)

Var< database_id >är databasens identifierare. För att ta reda på databas-ID måste du köra skriptet

välj namn, databas_id från sys . databaser

det kommer att returnera oss en lista över databaser och deras identifierare.

Vi får spåret igen:

Textuell representation av frågeplanen:

Grafisk representation av frågeplanen:

Som du kan se, erhölls frågeplanen på nytt av optimeraren, och den gamla cachelagrade användes inte, exekveringstiden återgick till det normala, liksom antalet avläsningar. Det är inte klart vad som orsakade det, kanske ett stort antal byten eller stängning av tidigare perioder, det är svårt att säga. Regelbundet databasunderhåll har konfigurerats. Det här är första gången jag stöter på en bluff med cachad frågeexekveringsplan.

Tack för din uppmärksamhet!

Hjälpte den här artikeln dig?

6 svar

Det finns flera sätt att få en genomförandeplan, som beror på dina omständigheter. Vanligtvis kan du använda SQL Server Management Studio för att skaffa planen, men om du av någon anledning inte kan köra din fråga i SQL Server Management Studio kan det vara användbart att skaffa planen via SQL Server Profiler eller genom att kontrollera planen cache.

Metod 1 - Använda SQL Server Management Studio

SQL Server har några snygga funktioner som gör det enkelt att samla in en exekveringsplan, se bara till att menyalternativet "Inkludera faktisk exekveringsplan" (finns i Query-menyn) är markerat och kör din som vanligt.

Om du försöker få exekveringsplanen för uttalanden i en lagrad procedur, skulle du köra den lagrade proceduren så här:

Exec p_Exempel 42

När din fråga är klar kommer du att se ytterligare en Execution Plan-flik i resultatfönstret. Om du har kört många godkännanden kan du se många planer visas på den här fliken.

Här kan du kontrollera exekveringsplanen i SQL Server Management Studio eller högerklicka på planen och välja "Spara exekveringsplan som..." för att spara planen till en XML-fil.

Metod 2 - Använda SHOWPLAN-alternativ

Denna metod är väldigt lik metod 1 (det är faktiskt vad SQL Server Management Studio gör internt), men jag inkluderade den för fullständighetens skull eller om du inte har SQL Server Management Studio tillgänglig.

Innan du kör frågan, kör ett följande operatörer. Utlåtandet måste vara det enda påståendet i paketet, d.v.s. Du kan inte köra en annan sats samtidigt:

SÄTT SHOWPLAN_TEXT PÅ SÄTT SHOWPLAN_ALL PÅ SÄTT SHOWPLAN_XML PÅ SET STATISTICS PROFILE ON SET STATISTICS XML PÅ -- Det rekommenderas alternativet att använda

Dessa är anslutningsparametrarna, så du behöver bara köra detta en gång för varje anslutning. Från och med nu kommer alla lanserade uttalanden att åtföljas av ytterligare uppsättning resultat som innehåller din genomförandeplan i det format som krävs - kör bara din fråga som vanligt för att se planen.

När du är klar kan du inaktivera det här alternativet med följande uttalande:

UPPSÄTTNING<> AV

Jämförelse av utförandeplansformat

Om du har en stark preferens rekommenderar jag att du använder alternativet STATISTICS XML. Det här alternativet motsvarar alternativet "Inkludera faktisk exekveringsplan" i SQL Server Management Studio och ger mest information i det mest användbara formatet.

• SHOWPLAN_TEXT - Visar den grundläggande textbaserade uppskattade exekveringsplanen utan att köra frågan
• SHOWPLAN_ALL - Visar en uppskattad textbaserad utförandeplan med en kostnadsuppskattning utan att köra frågan
• SHOWPLAN_XML - Visar en XML-baserad beräknad utförandeplan med kostnadsuppskattningar utan att exekvera frågan. Detta motsvarar alternativet "Visa exempel på exekveringsplan..." i SQL Server Management Studio.
• STATISTIKPROFIL - Utför frågan och visar den faktiska utförandeplanen baserat på texten.
• STATISTIK XML - Exekverar frågan och visar den faktiska exekveringsplanen baserad på XML. Detta motsvarar alternativet "Inkludera faktisk exekveringsplan" i SQL Server Management Studio.

Metod 3 - Använda SQL Server Profiler

Om du inte kan köra frågan direkt (eller din fråga inte körs långsamt när du kör den direkt - kom ihåg att vi vill att frågeplanen ska köras dåligt), så kan du fånga planen med SQL Server Profiler. Tanken är att köra din fråga medan ett spår som fångar en av "Showplan"-händelserna körs.

Observera att beroende på belastningen du du kan använd den här metoden i en produktionsmiljö, men du bör självklart vara försiktig. SQL Servers profileringsmekanismer är utformade för att minimera påverkan på databasen, men det betyder inte att det inte kommer att bli någon prestandapåverkan. Du kan också ha problem med att filtrera och bestämma rätt plan i ditt spår om din databas är under hög användning. Du bör självklart kolla med din DBA för att se till att de är nöjda med att du gör detta till din värdefulla databas!

• Öppna SQL Server Profiler och skapa ett nytt spår som ansluter till den önskade databasen som du vill spela in spåret från.
• På fliken Event Selection, markera kryssrutan Visa alla händelser, markera raden Prestanda -> Showplan XML och kör spårningen.
• Medan spårningen körs, gör allt du behöver göra för att få den långsamma frågan att köras.
• Vänta tills begäran är klar och spårningen upphör.
• För att spara spårningen, högerklicka på xml-planen i SQL Server-profilen och välj "Extrahera händelsedata..." för att spara planen i en XML-fil.

Planen du får motsvarar alternativet "Inkludera faktisk exekveringsplan" i SQL Server Management Studio.

Metod 4 - Kontrollera frågecachen

Om du inte kan köra din fråga direkt, och du inte heller kan fånga en profileringsspårning, bör du fortfarande kunna få en uppskattad plan genom att kontrollera SQL-frågans cacheplan.

Vi kontrollerar planens cache genom att fråga SQL Server DMV:er. Nedan finns en grundläggande fråga som listar alla cachade frågeplaner (som xml) tillsammans med deras SQL-text. I de flesta databaser måste du också lägga till ytterligare filtervillkor för att filtrera ner resultaten till de planer du är intresserad av.

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Kör den här frågan och klicka på XML-planen för att öppna planen i ett nytt fönster - högerklicka och välj "Spara Execution Plan As..." för att spara planen i en fil i XML-format.

Anmärkningar:

Eftersom det finns så många faktorer (från tabell- och indexschema till lagrad data och tabellstatistik), måste du Alltid försök att hämta exekveringsplanen från databasen du är intresserad av (vanligtvis den som har prestandaproblemet).

Du kan inte utföra exekveringsplanen för krypterade lagrade procedurer.

"faktiska" och "beräknade" utförandeplaner

Den faktiska exekveringsplanen är den där SQL Server faktiskt exekverar frågan, medan den beräknade exekveringsplanen SQL Server fungerar på vad den skulle kunna göra utan att exekvera frågan. Även om den är logiskt likvärdig, är den faktiska exekveringsplanen mycket mer användbar eftersom den innehåller ytterligare data och statistik om vad som faktiskt hände när frågan kördes. Detta är viktigt vid diagnostisering av problem när SQL-serverutvärderingar är inaktiverade (till exempel när statistiken är inaktuell).

Hur tolkar man utförandeplanen för frågor?

Detta är ett ämne värdigt nog för en gratis bok.

Förutom det omfattande svaret som redan postats ibland, är det användbart att kunna komma åt exekveringsplanen programmatiskt för att extrahera information. Exempelkod för detta finns nedan.

DECLARE @TraceID INT EXEC StartCapture @@SPID, @TraceID OUTPUT EXEC sp_help "sys.objects" /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID

Mitt favoritverktyg för att erhålla och fördjupad analys av utförandeplaner för frågor är SQL Sentry Plan Explorer. Det är mycket mer bekvämt, användarvänligt och komplett för detaljerad analys och visualisering av genomförandeplaner än SSMS.

Här är en exempelskärm där du kan förstå vilken funktion som verktyget erbjuder:

Detta är bara en av vyerna som finns tillgängliga i verktyget. Lägg märke till uppsättningen flikar längst ner i programfönstret som låter dig få olika typer av exekveringsplanvyer och användbar ytterligare information.

Dessutom har jag inte märkt några begränsningar i dess gratisversion som hindrar dig från att använda den dagligen eller tvingar dig att köpa Pro-versionen så småningom. Så om du föredrar att hålla fast vid gratisversionen är ingenting förbjudet.

Förutom metoderna som beskrivits i tidigare svar kan du också använda den kostnadsfria exekveringsplanvisaren och frågeoptimeringsverktyget ApexSQL Plan (som jag nyligen har stött på).

Du kan installera och integrera ApexSQL-planen i SQL Server Management Studio, så att exekveringsplaner kan ses direkt från SSMS.

Visa förutspådda exekveringsplaner i ApexSQL Plan

• Klicka på knappen Ny förfrågan i SSMS och klistra in frågetexten i frågetextrutan. Högerklicka och välj "Visa exempel på exekveringsplan" från snabbmenyn.

1. Utförandeplansdiagrammet visar fliken Utförandeplanering i resultatsektionen. Högerklicka sedan på exekveringsplanen och välj alternativet "Öppna i ApexSQL-plan" från snabbmenyn.

1. Den beräknade exekveringsplanen kommer att öppnas i ApexSQL-planen och kan analyseras för att optimera frågor.

Visa faktiska exekveringsplaner i en ApexSQL-plan

För att se den faktiska exekveringsplanen för frågor, gå till det andra steget som nämnts tidigare, men nu, när den beräknade planen visas, klicka på knappen "Faktisk" på huvudfältet i ApexSQL-planen.

Efter att ha klickat på knappen Faktisk kommer den faktiska utförandeplanen att visas med en detaljerad förhandsgranskning av kostnadsparametrarna tillsammans med andra data om utförandeplanen.

Mer information om visning av genomförandeplaner kan hittas genom att följa denna länk.

Frågeplaner kan erhållas från en utökad händelsesession genom händelsen query_post_execution_showplan. Här är ett exempel på XEvent-session:

/* Genereras via mallen "Spårning av frågedetaljer". */ SKAPA HÄNDELSESESSION PÅ SERVER LÄGG TILL HÄNDELSE sqlserver.query_post_execution_showplan(ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,ql_server,sql_server,sqlserver fram,sqlserver tsql_stack)), / * Ta bort någon av följande händelser (eller inkludera ytterligare händelser) efter önskemål. */ ADD EVENT sqlserver.error_reported(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sqlserver,sqlserver.session_id,slserver.sqlserver qlserver.tsql_stack ) VAR (.(.,(4)) OCH .(.,(0)))), LÄGG TILL HÄNDELSE sqlserver.module_end(SET collect_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver . plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) VAR (.(.,(4)) OCH .)(., DD)ENT sqlserver.rpc_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sqlserver,sqlserver.sqlserver.sqlserver.sqlserver. l_stack) VAR (. ( .,(4)) OCH .(.,(0))), ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan.handle,sqlserver,sqlserver,sqlserver query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) VAR (.(.,(4)) OCH .(.,(0)))), LÄGG TILL EVENT.COM(0))) , sqlchENT.com ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.databas_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsq)Wl_server.tsq) ( 4 )) OCH .(.,(0)))), LÄGG TILL HÄNDELSE sqlserver.sql_statement_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash.query_hash,sqlserverid,sqlserverid , sqlserver.sql_text, sqlserver.tsql_frame, sqlserver.tsql_stack) VAR (.(.,(4)) OCH .(.,(0))) LÄGG TILL MÅLpaket0.ring_buffer MED (MAX_MEMORY=4096 KB,EVENT_REALLDISCHENT, EVENT_FÖRHÅLLANDENVÄNDSKRIVNING) ATENCY =30 SEKUNDER,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=INGEN,TRACK_CAUSALITY=PÅ,STARTUP_STATE=AV) GÅ

När sessionen har skapats (i SSMS), gå till Objektläsare och gå till Hantera | Utökade evenemang | Sessioner. Högerklicka på "GetExecutionPlan"-sessionen och kör den. Högerklicka på den och välj "Titta på livedata".

Öppna sedan ett nytt frågefönster och kör en eller flera frågor. Här är en för AdventureWorks:

ANVÄND AdventureWorks; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderAntal * UnitPrice), Rabatter = ((OrderQuty * UnitPrice) * UnitPriceRabatt) FRÅN Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = Sod.ProduktID ORDER Produktnamn DESC; GÅ

Efter en minut eller två kommer du att se några resultat på fliken "GetExecutionPlan: Live Data". Välj en av query_post_execution_showplan-händelserna i rutnätet och klicka sedan på fliken Frågeplan under rutnätet. Det borde se ut ungefär så här:

REDIGERA: XEvent-koden och skärmdumpen genererades från SQL/SSMS 2012 w/SP2. Om du använder SQL 2008/R2 kan du ställa in ett skript för att köra det. Men den här versionen har inget GUI, så du måste extrahera showplan XML-filen, spara den som en *.sqlplan-fil och öppna den i SSMS. Det är krångligt. XEvents fanns inte i SQL 2005 eller tidigare. Så, om du inte använder SQL 2012 eller senare, skulle jag starkt föreslå ett av de andra svaren som publiceras här.

dela med sig

Frågeoptimering i SQL Server 2005, SQL Server 2005 databasstatistik, SKAPA STATISTIK, UPPDATERA STATISTIK, SÄTTA ANTAL PÅ, exekveringsplaner för frågor, antal logiska läsningar, optimeringstips, MAXDOP, OPTIMERA FÖR, självstudier exekveringsplaner (plan spp_guides),_guide

Om alla andra metoder för att optimera prestanda redan är uttömda, har SQL Server-utvecklare och administratörer en sista reserv till sitt förfogande - att optimera exekveringen av individuella frågor. Till exempel, om din uppgift absolut kräver att skapa en specifik rapport snabbare, kan du analysera frågan som används för att skapa denna rapport och försöka ändra planen om den inte är optimal.

Många specialister har en tvetydig inställning till frågeoptimering. Å ena sidan orsakar driften av programvarumodulen Query Optimizer, som genererar frågekörningsplaner, många rättvisa kritik i både SQL Server 2000 och SQL Server 2005. Query Optimizer väljer ofta inte de mest optimala frågekörningsplanerna och förlorar i vissa situationer till liknande moduler från Oracle och Informix. Å andra sidan är manuell frågeoptimering en extremt arbetskrävande process. Du kan spendera mycket tid på sådan optimering och i slutändan ta reda på att ingenting var optimerat: planen som ursprungligen föreslogs av Query Optimizer visade sig vara den mest optimala (detta händer i de flesta fall). Dessutom kan det hända att frågeexekveringsplanen som du skapade manuellt efter en tid (efter att ha lagt till ny information i databasen) kommer att visa sig vara suboptimal och kommer att minska prestandan vid exekvering av frågor.

Observera också att Query Optimizer kräver korrekt statistisk information för att välja de bästa frågeplanerna. Eftersom, enligt författarens erfarenhet, inte alla administratörer vet vad det är, kommer vi att berätta mer om statistiken.

Statistik- detta är speciell serviceinformation om distributionen av data i tabellkolumner. Låt oss till exempel föreställa oss att en förfrågan utförs som bör returnera alla Ivanovs som bor i staden St. Petersburg. Låt oss anta att 90 % av posterna i den här tabellen har samma värde i kolumnen Stad - "Sankt Petersburg". Naturligtvis, med tanke på utförande av frågor, är det först mer lönsamt att välja alla Ivanovs i tabellen (de kommer uppenbarligen inte att vara 90%) och sedan kontrollera värdet på kolumnen Stad för varje vald post. Men för att ta reda på hur värdena i en kolumn är fördelade måste du först köra en fråga. Därför initierar SQL Server självständigt exekveringen av sådana frågor och lagrar sedan information om distributionen av data (vilket kallas statistik) i databastjänsttabellerna.

För SQL Server 2005-databaser är standardinställningarna AUTO_CREATE_STATISTICS Och AUTO_UPDATE_STATISTICS. I det här fallet kommer statistik för databaskolumner att skapas och uppdateras automatiskt. För de största och viktigaste databaserna kan det vara så att operationer för att skapa och uppdatera statistik kan störa den aktuella användarupplevelsen. Därför, för sådana databaser, är ibland dessa parametrar inaktiverade, och operationer för att skapa och uppdatera statistik utförs manuellt på natten. Kommandona som används för detta är SKAPA STATISTIK Och UPPDATERA STATISTIK.

Låt oss nu prata om frågeoptimering.

Det första du ska göra är att hitta de frågor som i första hand är föremål för optimering. Det enklaste sättet att göra detta är med hjälp av en profilerare, ställa in ett filter för varaktigheten av begäran (filter Varaktighet i fönstret RedigeraFiltrera(Redigera filter), som kan öppnas med knappen KolumnFilter på fliken evenemangUrval fönstret spåra sessionsegenskaper). Till exempel kan kandidater för optimering inkludera frågor vars exekveringstid var mer än 5 sekunder. Du kan också använda frågeinformationen från Database Tuning Advisor.

Därefter måste du kontrollera om parametern är inställd för dina anslutningar, lagrade procedurer och funktioner NOCOUNT. Du kan installera det med kommandot STÄLL IN NOCOUNT PÅ. När du ställer in denna parameter inaktiveras för det första returen från servern och visningen av information om antalet rader i frågeresultaten (d.v.s. raden "N rad(er) påverkade" på fliken Meddelanden(C-meddelanden) fönster för att arbeta med kod vid exekvering av en begäran i Management Studio). För det andra är överföringen av ett speciellt servermeddelande inaktiverat DONE_IN_PROC, som returneras som standard för varje lagrat procedursteg. När du anropar de flesta lagrade procedurer behöver du bara resultatet av deras exekvering, och ingen bryr sig om antalet rader som behandlas för varje steg. Ställ därför in parametern NOCOUNT för lagrade procedurer kan avsevärt förbättra deras prestanda. Exekveringshastigheten för vanliga frågor ökar också, men i mindre utsträckning (upp till 10%).

Efter detta kan du börja arbeta med exekveringsplaner.

Det enklaste sättet att se frågeexekveringsplanen är från SQL Server Management Studio. För att få information om den förväntade exekveringsplanen för frågor kan du använda menyn Fråga(Fråga) välj kommando VisaBeräknadAvrättningPlanen(Visa förväntad utförandeplan). Om du vill veta den faktiska planen för att utföra en fråga, kan du ställa in parametern i samma meny innan du kör den OmfattaFaktiskAvrättningPlanen(Inkludera verklig utförandeplan). I det här fallet, efter att ha kört frågan, kommer en annan flik att visas i resultatfönstret i SQL Server Management Studio AvrättningPlanen(Execution Plan), som visar den faktiska frågeexekveringsplanen. När du för musen över någon av stegen kan du få ytterligare information om det (Fig. 11.15).

Ris. 11.15. Frågeexekveringsplan i SQL Server Management Studio

I en frågeexekveringsplan, som du kan se i figuren, kan det finnas många element. Att förstå dem, liksom att föreslå en annan utförandeplan, är en ganska svår uppgift. Det måste sägas att var och en av de möjliga elementen är optimal i sin egen situation. Därför ser vanligtvis stadierna av frågeoptimering ut så här:

q Kör först kommandot i Management Studio-fönstret SÄTTA PÅ STATISTIK IO. Som ett resultat kommer ytterligare information att visas efter varje utförande av begäran. I den är vi intresserade av värdet av endast en parameter - Logisk läsning. Denna parameter betyder antalet logiska läsningar vid exekvering av frågor, det vill säga hur många läsoperationer som behövdes utföras vid exekvering av en given fråga utan att ta hänsyn till påverkan av cachen (antalet läsningar från både cachen och disken). Detta är den viktigaste parametern. Antalet fysiska läsningar (endast läser från disken) är inte särskilt representativ information, eftersom det beror på om det fanns tidigare åtkomster till dessa tabeller eller inte. Tidsstatistik är också variabel och beror på andra operationer som servern utför vid den tidpunkten. Men antalet logiska läsningar är den mest objektiva indikatorn, som minst påverkas av ytterligare faktorer;

q försök sedan ändra frågeexekveringsplanen och ta reda på det totala antalet logiska läsningar för varje plan. Vanligtvis ändras frågeexekveringsplanen med hjälp av optimeringstips. De berättar uttryckligen för optimeraren vilken exekveringsplan som ska användas.

Det finns många optimeringstips i SQL Server 2005. Du kan läsa information om dem i Books Online (i listan på fliken Index(Index) måste väljas FrågaTips [SQLServer](Frågetips), Ansluta sigTips(Gå med tips) eller TabellTips [SQLServer](Tabelltips)). De vanligaste tipsen är:

q NOLOCK, ROWLOCK, PAGLOCK, TABELL, HOLDLOCK, LÄSKOMMITTERAD LÅS, LÅS UPP, XLOCK- dessa tips används för att hantera lås (se avsnitt 11.5.7);

q SNABB antal rader - en frågeexekveringsplan kommer att väljas där det angivna antalet rader (den första från början av postuppsättningen) kommer att visas så snabbt som möjligt. Om användaren behöver exakt de första posterna (till exempel de senaste beställningarna), kan detta tips användas för att ladda dem i applikationsfönstret så snabbt som möjligt;

q TVÄNGA BESTÄLLNING- sammanfogning av tabeller vid exekvering av en fråga kommer att utföras exakt i den ordning som dessa tabeller är listade i frågan;

q MAXDOP(från Maximum Degree of Parallelism - den maximala graden av parallellisering av en begäran) - med hjälp av denna ledtråd indikeras det maximala antalet processorer som kan användas för att exekvera begäran. Vanligtvis används det här tipset i två situationer:

· vid byte mellan processorer ( sammanhangväxlande) hastigheten för exekvering av frågor reduceras avsevärt. Detta beteende var typiskt för SQL Server 2000 på flerprocessorsystem;

· när du vill att en tung begäran ska ha minimal inverkan på den nuvarande användarupplevelsen;

q OPTIMERA FÖR- denna ledtråd låter dig ange att begäran är optimerad för ett specifikt värde av parametern som skickas till den (till exempel för filtervärdet för VAR);

q ANVÄND PLAN– det här är den mest kraftfulla möjligheten. Med hjälp av en sådan ledtråd kan du uttryckligen definiera frågeexekveringsplanen genom att skicka planen som ett strängvärde i XML-format. Antydan ANVÄND PLAN dök endast upp i SQL Server 2005 (i tidigare versioner var det möjligt att uttryckligen definiera exekveringsplaner, men detta gjordes på andra sätt). En plan i XML-format kan skrivas manuellt, eller så kan den genereras automatiskt (till exempel genom att högerklicka på den grafiska skärmen med exekveringsplanen som visas i Fig. 11.15 och välja kommandot i snabbmenyn SparaAvrättningPlanenSom(Spara utförandeplan som)).

SQL Server 2005 introducerar en viktig ny funktion som gör att du manuellt kan ändra frågeexekveringsplanen utan att behöva manipulera frågetexten. Det händer ofta att förfrågningskoden inte kan ändras: den är kopplad till koden för det kompilerade programmet. För att bekämpa detta problem introducerade SQL Server 2005 den lagrade proceduren sp_create_plan_guide. Det låter dig skapa sk Guider för genomförandeplan (planenguider), som automatiskt tillämpas på matchande förfrågningar.

Om du analyserar frågor som skickas till en databas av ett program, är det vettigt att först uppmärksamma följande punkter:

q hur ofta operationen förekommer i frågeexekveringsplanerna TabellSkanna(Fullständig tabellskanning). Det kan mycket väl visa sig att det blir mer effektivt att komma åt en tabell med hjälp av index;

q om markörer används i koden. Markörer är väldigt enkla när det gäller programsyntax, men extremt ineffektiva när det gäller prestanda. Mycket ofta kan du undvika att använda markörer genom att använda andra syntaktiska konstruktioner, och få en stor ökning i hastighet;

q om koden använder temporära tabeller eller en datatyp Tabell. Att skapa tillfälliga tabeller och arbeta med dem kräver mycket resurser, så du bör undvika dem om möjligt;

q Förutom att skapa temporära tabeller kräver en förändring av deras struktur också en betydande förbrukning av systemresurser. Därför bör kommandon för att ändra strukturen för tillfälliga tabeller omedelbart locka din uppmärksamhet. Det är vanligtvis möjligt att omedelbart skapa en tillfällig tabell med alla nödvändiga kolumner;

q ibland returnerar frågor mer data än vad programmet faktiskt behöver (extra kolumner eller rader). Detta förbättrar naturligtvis inte produktiviteten;

q om programmet skickar kommandon till servern KÖR, då är det vettigt att tänka på att ersätta dem med ett lagrat proceduranrop sp_executesql. Det har prestandafördelar jämfört med ett vanligt kommando KÖR;

q Prestandaförbättringar kan ibland uppnås genom att eliminera behovet av att kompilera om lagrade procedurer och bygga nya planer för exekvering av frågor. Du måste vara uppmärksam på användningen av parametrar, försök att inte blanda DML- och DDL-kommandon i koden för den lagrade proceduren och se till att anslutningsparametrarna STÄLL IN ANSI_DEFAULTS, SÄTT ANSI_NULLS, STÄLL IN ANSI_PADDING, STÄLL IN ANSI_WARNINGS Och STÄLL IN CONCAT_NULL_YIELDS_NULL har inte ändrats mellan begäranden (alla ändringar av sådana parametrar ogiltigförklarar de gamla exekveringsplanerna). Vanligtvis kan problemet uppstå när dessa parametrar ställs in på individuell begäran eller i lagrad procedurkod.

Observera att i vilket fall som helst, att skapa frågekörningsplaner manuellt och använda tips är en sista utväg och bör undvikas om möjligt.

SQL-förfrågningsplan, eller frågeplan, är en sekvens av steg eller DBMS-instruktioner som krävs för att exekvera en SQL-fråga. Vid varje steg hämtar operationen som initierade det här SQL-frågaexekveringssteget rader med data som kan bilda det slutliga resultatet eller användas för vidare bearbetning. Instruktioner för exekvering av SQL-frågor representeras som en sekvens av operationer som UTFÖRS av DBMS FOR SQL-satser SELECT, INSERT, radera och uppdatering. Innehållet i en frågeplan representeras vanligtvis i en trädstruktur och inkluderar följande information:

• ordningen för att ansluta datakällor (tabeller, vyer, etc.);
• åtkomstmetod för varje datakälla;
• metoder för att koppla datakällor;
• operationer för att begränsa dataurval, sortering och aggregering;
• kostnad och svårighetsgrad för varje operation;
• möjlig användning av partitionering och parallellism. Informationen som tillhandahålls av exekveringsplanen för SQL-frågor gör att utvecklaren kan se vilka tillvägagångssätt och metoder som optimeraren väljer för att utföra SQL-operationer.

Tolka SQL Query Execution Plan

Visualisering av exekveringsplanen för en SQL-fråga beror på verktyg och utvecklingsverktyg, som antingen kan vara en del av DBMS vars fråga är av intresse för analys, eller vara separata kommersiella eller fritt distribuerade mjukvaruprodukter som inte är direkt relaterade till en specifik DBMS tillverkare. Att använda ett eller annat visualiseringsverktyg för frågeplan påverkar vanligtvis inte nämnvärt uppfattningen av vad den presenterade frågeplanen beskriver. Den avgörande faktorn i processen att analysera vilken väg optimeraren kommer att ta när en specifik fråga körs är förmågan att korrekt tolka informationen som presenteras i frågeplanen.

Som redan nämnts har en SQL-frågeplan en trädstruktur som inte bara beskriver sekvensen för exekveringen av SQL-operationer, utan även relationerna mellan dessa operationer. Varje nod i frågeplansträdet är en operation, till exempel en sortering eller en tabellåtkomstmetod. Det finns en förälder-barn-relation mellan noder. Relationer mellan föräldrar och barn regleras av följande regler:

• en förälder kan ha ett eller flera barn;
• ett barn har bara en förälder;
• en operation som inte har någon överordnad operation är toppen av trädet;
• Beroende på metoden för att visualisera SQL-frågeplanen placeras barnet med en viss indragning i förhållande till föräldern. En förälders ättlingar finns på samma avstånd från sin förälder.

Låt oss ta en närmare titt på informationen som tillhandahålls av exekveringsplanen för SQL-frågor. Exemplen som ges utfördes i Oracle DBMS-miljön. Oracle SQL Developer användes som ett verktyg för att köra frågor och visualisera SQL-frågeplanen. Ett fragment av en SQL-frågeplan visas i fig. 10.11.

I ID I Operation

• 0RDER_ITEMS

PR0DUCT_INF0RMATI0N_PK PRODUKTINFORMATION

VÄLJ UTTALANDE SORTERINGSORDNING EFTER NESTADE SLÖGAR NESTADE LOCKAR TABELLÅTKOMST FULLSTÄNDIGT INDEX UNIK SKANNINGTABELLÅTKOMST EFTER INDEX ROWID

Ris. 10.11. Fragment av en SQL-frågeexekveringsplan i Oracle DBMS-miljön

Med hjälp av relationsreglerna för frågeplansoperationer kan vi definiera följande formella beskrivning av dem.

Operation 0 är roten till frågeplansträdet. Roten har ett barn: operation 1.

Operation 1 - operation har ett barn: operation 2.

Operation 2 - operationen har två barn: operation 3 och operation 6.

Operation 3 - operationen har två barn: operation 4 och operation 5.

Operation 4 - operationen har inga barn.

Operation 5 - operationen har inga barn.

Operation 6 - operationen har inga barn.

Förälder-barn-interaktionen mellan frågeplansoperationer visas i fig. 10.12.

Operationerna som utförs i en frågeplan kan delas in i tre typer: fristående, obundna joinoperationer och länkade joinoperationer (Figur 10.13).

Autonom		Verksamhet som inte är relaterad		Relaterad verksamhet
operationer		föreningar		föreningar

Ris. 10.12.

Ris. 10.13.

Autonoma operationer - Det är operationer som har högst en barnoperation.

Följande regler för vilka autonoma operationer utförs kan formuleras enligt följande.

• 2. Varje underordnad operation utförs endast en gång.
• 3. Varje underordnad operation returnerar sitt resultat till den överordnade operationen.

I fig. Figur 10.14 visar planen för följande fråga:

VÄLJ o.order_id ,o.order_status FRÅN order o BESTÄLL MED o.order_status

Den här frågan innehåller endast fristående operationer.

Med hänsyn till reglerna för att följa autonoma operationer kommer sekvensen för deras utförande att vara som följer.

• 1. I enlighet med regeln att följa autonoma operationer nr 1, kommer operationen med ID = 2 att utföras först. Alla rader i ordertabellen läses sekventiellt.
• 2. Därefter utförs operationen med ID = 1. Raderna som returneras av operationen med ID = 2 sorteras enligt villkoren i sorteringssatsen ORDER BY.
• 3. Operationen med ID = 0 utförs. Den resulterande datamängden returneras.

Obunden facklig verksamhet

Obunden facklig verksamhetär operationer som har mer än en självständigt exekverande underordnad operation. Exempel: HASH JOIN, MERGE JOIN, INTERSECTION, MINUS, UNION ALLA.

Följande regler enligt vilka obundna sammanfogningsoperationer fungerar kan formuleras enligt följande.

• 1. Den underordnade operationen exekveras före den överordnade operationen.
• 2. Underordnade operationer exekveras sekventiellt, med början med det minsta operations-ID-värdet i stigande ordning av dessa värden.
• 3. Innan varje efterföljande underordnad operation startar, måste den aktuella operationen slutföras helt.
• 4. Varje underordnad operation utförs endast en gång, oavsett andra underordnade operationer.
• 5. Varje underordnad operation returnerar sitt resultat till den överordnade operationen.

I fig. Figur 10.15 visar planen för följande fråga:

VÄLJ o.order_id från beställningar o UNION ALLA

VÄLJ oi.order_id från order_items oi

Den här frågan innehåller en obunden kopplingsoperation UNION alla. De återstående två operationerna är autonoma.

Ris. 10.15. Obundna sammanfogningsoperationer, frågeplan

	1 VÄLJ UTTALANDE I

Med tanke på reglerna för att följa orelaterade anslutningsoperationer kommer sekvensen för deras exekvering att vara som följer.

• 1. I enlighet med reglerna 1 och 2 för följande orelaterade joinoperationer, kommer operationen med ID = 2 att utföras först. Alla rader i ordertabellen läses sekventiellt.
• 2. I enlighet med regel 5 returnerar operationen med ID = 2 raderna i den överordnade operationen med ID = 1 läst i steg 1.
• 3. Operationen med ID = 3 kommer att börja utföras först när operationen med ID = 2 slutar.
• 4. Efter att ha slutfört operationen med ID = 2 börjar operationen med ID = 3. Alla rader i tabellen order_items läses sekventiellt.
• 5. I enlighet med regel 5 returnerar operationen med ID = 3 raderna i den överordnade operationen med ID = 1 läst i steg 4.
• 6. Operationen med ID = 1 genererar en resultatuppsättning data baserat på data som tas emot från alla dess underordnade operationer (med ID = 2 och ID = 3).
• 7. Operationen med ID = 0 utförs. Den resulterande datamängden returneras.

Det kan således noteras att den oberoende sammanfogningsoperationen exekverar sina underordnade operationer sekventiellt.

Linked Join Operations

Länkade anslutningsoperationer - Detta är operationer som har mer än en underordnad operation, där en av operationerna styr utförandet av de andra. Exempel: kapslade loopar, uppdatering.

Följande regler enligt vilka kedjade sammanfogningar fungerar kan formuleras enligt följande.

• 1. Den underordnade operationen exekveras före den överordnade operationen.
• 2. Den underordnade operationen med det lägsta operationsnumret (ID) styr exekveringen av de återstående underordnade operationerna.
• 3. Underordnade operationer som har en gemensam överordnad operation exekveras med början med det lägsta värdet av operations-ID:t i stigande ordning av dessa värden. De återstående underordnade operationerna exekveras INTE sekventiellt.
• 4. Endast den första underordnade operationen utförs en gång. Alla andra underordnade operationer utförs flera gånger eller inte alls.

I fig. Figur 10.16 visar planen för följande fråga:

FRÅN order_items oi, order o

WHERE o.order_id= oi.order_id

OCH oi.product_id>100

OCH o.customer_id mellan 100 och 1000

Den här frågan innehåller en relaterad kopplingsoperation, NESTED LOOPS.

I ID I Operation

		VÄLJ UTTALANDE |

Ris. 10.16. Länkade join-operationer, frågeplan

Med tanke på reglerna för att följa kedjade anslutningsoperationer kommer sekvensen för deras utförande att vara som följer.

• 1. Enligt reglerna 1 och 2 för följande chained join-operationer måste operationen med ID = 2 utföras först. Operationer med 1D = 2 och 1D = 3 är dock autonoma, och enligt regel 1 för följande autonoma operationer operation med ID = 2 kommer att utföras först. ID = 3. ORDCUSTOMERIX-indexintervallet visas baserat på villkoret: o. kund-id mellan 100 och 1000.
• 2. Operationen med ID=3 återgår till den överordnade operationen (med Ш=2) en lista över Rowld-radidentifierare som erhölls i steg 1.
• 3. Operationen med ID = 2 läser rader i ordertabellen där Rowld-värdet matchar listan över Rowld-värden som erhölls i steg 2.
• 4. Operationen med ID = 2 returnerar läsraderna för den överordnade operationen (med ID = 1).
• 5. För varje rad som returneras av operationen med ID = 2, exekveras den andra underordnade operationen (med ID = 4) av operationen kapslade slingor. Det vill säga, för varje rad som returneras av en operation med ID = 2, utförs en fullständig sekventiell genomsökning av tabellen order_items för att hitta en matchning på join-attributet.
• 6. Steg 5 upprepas lika många gånger som antalet rader som returneras av operationen med ID = 2.
• 7. En operation med ID = 1 returnerar resultatet av den överordnade operationen (med ID = 0).
• 8. Operationen med ID = 0 utförs. Den resulterande datamängden returneras.

Beroende på komplexiteten hos den analyserade frågan kan dess genomförandeplan ha en ganska komplex struktur, som vid första anblicken verkar svår att tolka. Metodisk implementering av reglerna som beskrivs ovan och nedbrytning av operationer gör att du effektivt kan analysera exekveringsplanen för en SQL-fråga av vilken komplexitet som helst. Låt oss titta på ett exempel på en fråga som genererar en lista över kunder, antalet varor de köpt och deras totala kostnad:

VÄLJ s. kund_förnamn kundnamn,

COUNT(DISTINCT oi.product_id) som product_quty,

SUM(oi.quantity* oi.unit_price) som total_cost FROM oe.orders o INNER JOIN-kunder c PÅ

o.customer_id=c.customer_id

INNER JOIN oe.order_items oi ON o.order_id= oi.order_id GRUPPER AV c. cust_first_name

Sekvensen av operationer för denna frågeplan visas i fig. 10.17.

VÄLJ UTTALANDE I

SORTERA GRUPP EFTER YG

	BORDTILLGÅNG FULL
	INDEXOMRÅDE SCAN
TABELLÅTKOMST EFTER INDEX ROWIDd
BORDTILLGÅNG FULL

Ris. 10.17. Frågeplan, operationssekvens

Låt oss beskriva ett möjligt tillvägagångssätt för att tolka exekveringsplanen för 80b-frågan som presenteras i fig. 10.17. Detta tillvägagångssätt inkluderar två huvudsteg: sönderdelning av operationer i block och bestämning av operationsordningen.

I det första steget är det nödvändigt att dekomponera de operationer som utförs i block. För att göra detta hittar vi all den fackliga verksamheten, d.v.s. operationer som har mer än en underordnad operation (i fig. 10.17 är dessa operationer 2, 3 och 4), och separera dessa underordnade operationer i block. Som ett resultat, med hjälp av exemplet i fig. 10.17 får vi tre fackliga verksamheter och sju verksamhetsblock.

I det andra steget bestäms sekvensen för exekvering av block av operationer. För att göra detta måste du tillämpa reglerna för följande operationer som beskrivs ovan. Låt oss göra en serie överväganden angående utförandet av varje operation i förhållande till dess identifikationsnummer (III).

Operationen Ш = 0 är autonom och är föräldern till operationen сШ = 1.

Operation Yu = 1 är också autonom; är föräldern till operationen W = 2 och exekveras före operationen Y = 0.

Operation GO = 2 är en icke-relaterad unionsoperation och är den överordnade operationen för operationer Yu = 3, Yu = 8. Operation GO = 2 utförs före operation GO = 1.

Operation GO = 3 är en länkad unionsoperation, det är den överordnade operationen för operationer GO = 4, GO = 7. Operation GO = 3 utförs före operation GO = 2.

Operation GO = 4 är en länkad unionsoperation, det är den överordnade operationen för operationer GO = 5, GO = 6. Operation GO = 4 utförs före operation GO = 3.

Operation GO = 5 är en autonom operation, utförd före operation GO = 4.

Operation GO = 6 är en autonom operation, utförd före operation GO = 5.

Operation GO = 7 är en autonom operation, utförd efter exekvering av operationsblocket "C".

Operation GO = 8 är en autonom operation, utförd efter operationsblocket "E".

Baserat på ovanstående resonemang och följande regler formulerar vi sekvensen av utförda operationer:

• 1. Den autonoma operationen GO = 5 utförs först, se reglerna för sekvensen av associerade joinoperationer. Hela tabellen läses sekventiellt.
• 2. Resultatet av operationen GO = 5 - lästabellraderna - överförs till operationen GO = 4.
• 3. Operationen GO = 4 utförs: för varje rad som returneras av operationen GO = 5, utförs operationen GO = 6. Det vill säga, indexintervallet skannas mot join-attributet. Få en lista med radidentifierare Yaou1s1.
• 4. Resultatet av operationen GO = 4 överförs till operationen GO = 3. Det vill säga, listan med radidentifierare Kosh1s1 överförs.
• 5. Operationen GO = 3 utförs: för varje värde 11оу1с1 som returneras som ett resultat av operationen av operationsblocket "C", utförs operationen GO = 7, d.v.s. tabellrader läses från en given lista med radidentifierare ITMI, erhållen efter att ha utfört operationen Ш = 4.
• 6. Den autonoma operationen GO = 8 utförs - sekventiell läsning av hela tabellen.
• 7. En icke-relaterad joinoperation GO = 2 utförs: en join utförs genom att hasha resultaten av operationsblocken "E" och "E".
• 8. Resultatet av operation GO = 2 överförs till operation GO = 1.
• 9. Den orelaterade sammanfogningsoperationen GO = 1 utförs: data som erhålls som ett resultat av operationen GO = 2 aggregeras och sorteras.
• 10. Operationen GO = 0 utförs. Den resulterande datamängden returneras.

Följande regler formulerade för huvudtyperna av operationer är tillämpliga på de flesta planer för att utföra en BSGO-förfrågan. Det finns dock konstruktioner som används i BSGO-förfrågningar som innebär ett brott mot den ordning som beskrivs i följande regler. Sådana situationer kan uppstå som ett resultat av att man använder till exempel subqueries eller anti-join-predikat. Hur som helst innebär processen att tolka exekveringsplanen för en BSGO-fråga inte bara användningen av ett antal regler som säkerställer den mest exakta analysen av vad optimeraren kommer att göra när en BSGO-förfrågan körs. En annan BSGO-förfrågan är alltid ett individuellt fall; och hur det kommer att exekveras i DBMS beror på många faktorer, inklusive versionen av DBMS, versionen och typen av operativsystem som DBMS-instansen är distribuerad på, den hårdvara som används, kvalifikationerna för författaren till 80b-frågan, etc.

1 msdevcon.ru #msdevcon

3 Olontsev Sergey SQL Server MCM, MVP Kaspersky Lab

4 Structured Query Language

5 Exempelfråga välj pers.förnamn, pers.efternamn, emp.jobtitle, emp.nationalidnumber från HumanResources.Employee as emp inre join Person.Person as pers on pers.businessentityid = emp.businessentityid där pers.firstname = N"John" och emp.hiredate >= " "

6 Logiskt frågeträd Projekt pers.förnamn, pers.efternamn, emp.jobtitle, emp.nationalidnumber D A T A Filter Gå med pers.firstname = N"John" och emp.hiredate >= " " pers.businessentityid = emp.businessentityid Person.Person som pers Get Data Get Data HumanResources.Employee as emp

7 Frågeplan Visar hur en T-SQL-fråga exekveras på fysisk nivå.

8 Flera sätt

9 DEMO Enkel plan Välja all data från en tabell, hur man får en frågeplan

11 Init()-operatörsmetoder Init()-metoden får den fysiska operatören att initiera sig själv och förbereda alla nödvändiga datastrukturer. En fysisk operatör kan ta emot många samtal till Init(), även om den vanligtvis bara tar emot ett. GetNext() Metoden GetNext() gör att den fysiska operatorn hämtar den första eller efterföljande raden med data. En fysisk operatör kan ta emot många GetNext()-samtal eller inga. Metoden GetNext() returnerar en rad med data, och antalet gånger den anropas indikeras av värdet ActualRows i utdata från Showplan-satsen. Close() När metoden Close() anropas utför den fysiska operatören en del rensning och stänger. Den fysiska operatören tar bara emot ett anrop till Close().

12 Interaktion mellan operatörer Operatör 1 Operatör 2 Operatör 3

13 Interaktion mellan operatörer 1. Begär radoperatör 1 operatör 2 operatör 3

14 Interaktion mellan operatörer 1. Begär rad 2. Begär rad operatör 1 operatör 2 operatör 3

15 Interaktion mellan operatörer 1. Begär rad 2. Begär rad operatör 1 operatör 2 operatör 3 3. Skicka rad

16 Interaktion mellan operatörer 1. Begär rad 2. Begär rad operatör 1 operatör 2 operatör 3 4. Skicka rad 3. Skicka rad

17 Interaktion mellan operatörer 1. Begär rad 2. Begär rad operatör 1 operatör 2 operatör 3 4. Skicka rad 3. Skicka rad

18 DEMO Operatör TOP Eller varför det är bättre att kalla en operatör för en iterator

19 tabeller finns inte!

20 HoBT Sida 1 Sida 2 Sida 3 Sida 4 Rad 1 Rad 3 Rad 5 Rad 7 Rad 2 Rad 4 Rad 6 Rad 8

21 HoBT Sida Sida Sida Sida Sida Sida

22 DEMO Operatörer för dataåtkomst Scan, Seek, Lookup

23 Vem har bara en tabell i databasen?

24 kapslade loopar, Hash Join och Merge Join

25 Join Operators Nested Loops innerfog, vänster yttre sammanfogning, vänster halvfog, vänster anti semi sammanfogning Merge Join inner sammanfogning, vänster yttre sammanfogning, vänster halv sammanfogning, vänster anti semi sammanfogning, höger yttre sammanfogning, höger halv sammanfogning, höger anti semi sammanfogning , fackförening Hash Gå med i alla typer av logiska operationer

26 DEMO Gå med, sortera och första operatören Nested Loops, Merge Join, Hash Join, Sortera, First Operator

27 Varningar

28 DEMO Fel och varningar i frågeplaner

29 Jag vet att jag inte vet någonting. Sokrates

30 DEMO Ett litet exempel på något oklart

31 Diagnostisera frågeplaner -- TOP 10 frågor som förbrukar mest CPU och deras planer välj top(10) substring(t.text, qs.statement_start_offset / 2, case när qs.statement_end_offset = -1 sedan len(t.text) else (qs.statement_end_offset - qs.statement_start_offset) / 2 end), qs.execution_count, cast(qs.total_worker_time / as decimal(18, 2)) as total_worker_time_ms, cast(qs.total_worker_time * 1. / qs. (18, 2)) som avg_worker_time_ms, cast(p.query_plan som xml) som query_plan från sys.dm_exec_query_stats som qs cross applicera sys.dm_exec_sql_text(qs.sql_handle) som t cross applicera sys.dm_query_set. , qs.statement_end_offset) som p-ordning efter qs.total_worker_time desc; gå

32 Tekniker för att läsa stora frågeplaner Prova att dela upp dem i logiska block och analysera dem gradvis. I SSMS, när planen visas grafiskt, visas en knapp i det nedre högra hörnet för enklare navigering genom frågeplanen. Du kan använda XQuery\XPath.

33 DEMO Stor frågeplan

35 DEMO SQL Sentry Plan Explorer

36 Låt oss sammanfatta Första operatör Optimeringsnivå Kompileringstid Storlek i cache Parametrar, kompileringsvärden Orsak till tidig avslutning Kostnad för iteratorer Titta först på operatörerna med högst kostnad. Tänk på att dessa bara är uppskattade värden (även i faktiska utförandeplaner).

37 Låt oss sammanfatta Bookmark\Key Lookup Om det finns få av dem är det troligen inga problem. Om det finns många av dem, kan skapa ett täckande index hjälpa till att bli av med dem. Varningar Du måste kontrollera varför det inträffar och vidta åtgärder vid behov.

38 Låt oss sammanfatta Kopplingar mellan operatörer (dataflöden) Ju tjockare kopplingen är, desto mer data skickas mellan dessa operatörer. Det är särskilt värt att uppmärksamma om dataflödet i något skede ökar kraftigt. Ordning för att sammanfoga tabeller Ju mindre dataströmmar desto lättare är det att sammanfoga dem. Därför måste du först och främst gå med i de tabeller vars resulterande dataflöde blir mindre.

39 Sammanfattning Skanningar Skanningar betyder inte att det finns ett problem. Det är möjligt att det inte finns tillräckligt med index på tabellen för att göra en mer effektiv sökning. Å andra sidan, om du behöver välja hela eller en stor del av tabellen blir skanningen mer effektiv. Att söka betyder inte att allt är bra. Ett stort antal sökningar på icke-klustrade index kan vara ett problem. Allt du inte vet om planen kan potentiellt vara ett problem.

40 frågor

41 Kontakter Olontsev Sergey Kaspersky Lab

42 2013 Microsoft Corporation. Alla rättigheter förbehållna. Microsoft, Windows, Windows Vista och andra produktnamn är eller kan vara registrerade varumärken och/eller varumärken i U.S.A. och/eller andra länder. Informationen häri är endast avsedd för informationsändamål och representerar Microsoft Corporations nuvarande syn på datumet för denna presentation. Eftersom Microsoft måste reagera på förändrade marknadsförhållanden bör det inte tolkas som ett åtagande från Microsofts sida, och Microsoft kan inte garantera riktigheten av någon information som tillhandahålls efter datumet för denna presentation. MICROSOFT GER INGA GARANTIER, UTTRYCKLIGA, UNDERFÖRSTÅDDA ELLER LAGSTADIGADE, ANGÅENDE INFORMATIONEN I DENNA PRESENTATION.