W Microsoft SQL Server jak mogę uzyskać plan wykonania zapytania dla zapytania / procedury składowanej?

Istnieje wiele metod uzyskania planu wykonania, z których jeden będzie zależał od okoliczności. Zwykle można użyć programu SQL Server Management Studio, aby uzyskać plan, jednak jeśli z jakiegoś powodu nie można uruchomić zapytania w programie SQL Server Management Studio, pomocne może być uzyskanie planu za pomocą programu SQL Server Profiler lub inspekcji pamięć podręczna planu.

Metoda 1 - Korzystanie z SQL Server Management Studio

SQL Server zawiera kilka ciekawych funkcji, które bardzo ułatwiają uchwycenie planu wykonania, po prostu upewnij się, że element menu „Uwzględnij rzeczywisty plan wykonania” (znajdujący się w menu „Zapytanie”) jest zaznaczony i uruchom zapytanie w normalny sposób .

Jeśli próbujesz uzyskać plan wykonania instrukcji w procedurze przechowywanej, powinieneś wykonać procedurę przechowywaną w następujący sposób:

exec p_Example 42

Po zakończeniu zapytania w okienku wyników powinna pojawić się dodatkowa karta zatytułowana „Plan wykonania”. Jeśli uruchomiłeś wiele wyciągów, w tej zakładce możesz zobaczyć wiele planów.

Stąd możesz sprawdzić plan wykonania w SQL Server Management Studio lub kliknąć plan prawym przyciskiem myszy i wybrać „Zapisz plan wykonania jako ...”, aby zapisać plan w pliku w formacie XML.

Metoda 2 - Korzystanie z opcji SHOWPLAN

Ta metoda jest bardzo podobna do metody 1 (w rzeczywistości to właśnie robi SQL Server Management Studio wewnętrznie), jednak podałem ją dla kompletności lub jeśli nie masz dostępnego SQL Server Management Studio.

Przed uruchomieniem zapytania uruchom jedną z następujących instrukcji. Instrukcja musi być jedyną instrukcją w partii, tzn. Nie można wykonać innej instrukcji w tym samym czasie:

SET SHOWPLAN_TEXT ON
SET SHOWPLAN_ALL ON
SET SHOWPLAN_XML ON
SET STATISTICS PROFILE ON
SET STATISTICS XML ON -- The is the recommended option to use

Są to opcje połączenia, dlatego wystarczy raz uruchomić to połączenie. Od tego momentu wszystkim uruchomionym instrukcjom będzie towarzyszył dodatkowy zestaw wyników zawierający plan wykonania w żądanym formacie - po prostu uruchom zapytanie w normalny sposób, aby zobaczyć plan.

Po zakończeniu możesz wyłączyć tę opcję za pomocą następującego oświadczenia:

SET <<option>> OFF

Porównanie formatów planu wykonania

Jeśli nie masz silnych preferencji, zalecam skorzystanie z tej STATISTICS XMLopcji. Ta opcja jest równoważna opcji „Uwzględnij rzeczywisty plan wykonania” w SQL Server Management Studio i zapewnia najwięcej informacji w najwygodniejszym formacie.

• SHOWPLAN_TEXT - Wyświetla podstawowy tekstowy szacunkowy plan wykonania, bez wykonywania zapytania
• SHOWPLAN_ALL - Wyświetla szacunkowy plan wykonania oparty na tekście z szacunkami kosztów, bez wykonywania zapytania
• SHOWPLAN_XML- Wyświetla oparty na XML plan wykonania z oszacowaniami kosztów, bez wykonywania zapytania. Jest to równoważne opcji „Wyświetl przewidywany plan wykonania ...” w SQL Server Management Studio.
• STATISTICS PROFILE - Wykonuje zapytanie i wyświetla tekstowy plan wykonania.
• STATISTICS XML- Wykonuje zapytanie i wyświetla rzeczywisty plan wykonania oparty na XML. Jest to równoważne opcji „Uwzględnij rzeczywisty plan wykonania” w SQL Server Management Studio.

Metoda 3 - Korzystanie z SQL Server Profiler

Jeśli nie możesz uruchomić zapytania bezpośrednio (lub zapytanie nie uruchamia się powoli, gdy wykonujesz je bezpośrednio - pamiętaj, że chcemy, aby plan zapytania działał źle), możesz przechwycić plan przy użyciu śledzenia SQL Server Profiler. Chodzi o to, aby uruchomić zapytanie podczas działania śledzenia przechwytującego jedno ze zdarzeń „Showplan”.

Należy pamiętać, że w zależności od obciążenia można użyć tej metody w środowisku produkcyjnym, ale oczywiście należy zachować ostrożność. Mechanizmy profilowania programu SQL Server zostały zaprojektowane w celu zminimalizowania wpływu na bazę danych, ale nie oznacza to, że nie będzie żadnego wpływu na wydajność. Możesz również mieć problemy z filtrowaniem i identyfikacją odpowiedniego planu w swoim śladzie, jeśli Twoja baza danych jest intensywnie używana. Powinieneś oczywiście sprawdzić u swojego DBA, czy są zadowoleni z tego, że robisz to w ich cennej bazie danych!

1. Otwórz SQL Server Profiler i utwórz nowy ślad łączący się z żądaną bazą danych, dla której chcesz zapisać ślad.
2. W zakładce „Wybór zdarzeń” zaznacz „Pokaż wszystkie zdarzenia”, zaznacz wiersz „Wydajność” -> „Showplan XML” i uruchom śledzenie.
3. Gdy śledzenie jest uruchomione, rób wszystko, co musisz zrobić, aby uruchomić wolno działające zapytanie.
4. Poczekaj na zakończenie zapytania i zatrzymaj śledzenie.
5. Aby zapisać ślad, kliknij prawym przyciskiem myszy plan xml w SQL Server Profiler i wybierz „Wyodrębnij dane zdarzenia ...”, aby zapisać plan do pliku w formacie XML.

Otrzymany plan jest równoważny opcji „Dołącz rzeczywisty plan wykonania” w SQL Server Management Studio.

Metoda 4 - Sprawdzanie pamięci podręcznej zapytania

Jeśli nie możesz uruchomić zapytania bezpośrednio, a także nie możesz przechwycić śladu profilera, nadal możesz uzyskać szacunkowy plan, sprawdzając pamięć podręczną planu zapytań SQL.

Sprawdzamy cache planu przez zapytań SQL Server DMVs . Poniżej znajduje się podstawowe zapytanie, które wyświetli wszystkie buforowane plany zapytań (jako xml) wraz z ich tekstem SQL. W większości baz danych będziesz również musiał dodać dodatkowe klauzule filtrujące, aby odfiltrować wyniki do interesujących Cię planów.

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)

Wykonaj tę kwerendę i kliknij XML planu, aby otworzyć plan w nowym oknie - kliknij prawym przyciskiem myszy i wybierz „Zapisz plan wykonania jako ...”, aby zapisać plan do pliku w formacie XML.

Uwagi:

Ponieważ w grę wchodzi tak wiele czynników (od tabeli i schematu indeksu, aż po przechowywane dane i statystyki tabeli), zawsze powinieneś próbować uzyskać plan wykonania z bazy danych, którą jesteś zainteresowany (zwykle ten, który ma wydajność problem).

Nie można uchwycić planu wykonania dla zaszyfrowanych procedur przechowywanych.

„rzeczywiste” i „szacunkowe” plany wykonania

Rzeczywisty plan wykonania jest taki, w którym SQL Server rzeczywiście uruchamia kwerendę, podczas gdy szacuje się plan wykonania SQL Server działa, co to będzie zrobić bez wykonywania zapytania. Chociaż logicznie równoważny, rzeczywisty plan wykonania jest o wiele bardziej przydatny, ponieważ zawiera dodatkowe szczegóły i statystyki dotyczące tego, co faktycznie wydarzyło się podczas wykonywania zapytania. Jest to niezbędne przy diagnozowaniu problemów, w których szacunki serwerów SQL są wyłączone (na przykład gdy statystyki są nieaktualne).

• Szacowany i aktualny plan wykonania zmieniony

Jak interpretować plan wykonania zapytania?

Jest to temat wystarczająco godny samodzielnej (bezpłatnej) książki .

Zobacz też:

• Podstawy planu wykonania
• SHOWPLAN Permission i Transact-SQL Partie
• SQL Server 2008 - Używanie skrótów zapytań i skrótów planu zapytań
• Analiza bufora planu SQL Server

Oprócz wyczerpującej odpowiedzi, która została już opublikowana, czasem przydatne jest programowe uzyskanie dostępu do planu wykonania w celu wyodrębnienia informacji. Przykładowy kod tego jest poniżej.

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

Przykładowa StartCapturedefinicja

CREATE PROCEDURE StartCapture
@Spid INT,
@TraceID INT OUTPUT
AS
DECLARE @maxfilesize BIGINT = 5
DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36)

EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL 

exec sp_trace_setevent @TraceID, 122, 1, 1
exec sp_trace_setevent @TraceID, 122, 22, 1
exec sp_trace_setevent @TraceID, 122, 34, 1
exec sp_trace_setevent @TraceID, 122, 51, 1
exec sp_trace_setevent @TraceID, 122, 12, 1
-- filter for spid
EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid
-- start the trace
EXEC sp_trace_setstatus @TraceID, 1

Przykładowa StopCapturedefinicja

CREATE  PROCEDURE StopCapture
@TraceID INT
AS
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql), 
      CTE
     as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData,
                ObjectID,
                ObjectName,
                EventSequence,
                /*costs accumulate up the tree so the MAX should be the root*/
                MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
         FROM   fn_trace_getinfo(@TraceID) fn
                CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1)
                CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x
                CROSS APPLY (SELECT T.relop.value('@EstimatedTotalSubtreeCost',
                                            'float') AS EstimatedTotalSubtreeCost
                             FROM   xPlan.nodes('//sql:RelOp') T(relop)) ca
         WHERE  property = 2
                AND TextData IS NOT NULL
                AND ObjectName not in ( 'StopCapture', 'fn_trace_getinfo' )
         GROUP  BY CAST(TextData AS VARCHAR(MAX)),
                   ObjectID,
                   ObjectName,
                   EventSequence)
SELECT ObjectName,
       SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
FROM   CTE
GROUP  BY ObjectID,
          ObjectName  

-- Stop the trace
EXEC sp_trace_setstatus @TraceID, 0
-- Close and delete the trace
EXEC sp_trace_setstatus @TraceID, 2
GO

Zakładając, że używasz Microsoft SQL Server Management Studio

• W celu uzyskania szacunkowego planu zapytań możesz nacisnąć Ctrl + L lub następujący przycisk.

• W przypadku rzeczywistego planu zapytań możesz nacisnąć Ctrl + M lub następujący przycisk przed wykonaniem zapytania.

• W przypadku planu zapytań na żywo (tylko w SSMS 2016) użyj następującego przycisku przed wykonaniem zapytania.

Oprócz metod opisanych w poprzednich odpowiedziach możesz także użyć bezpłatnej przeglądarki planu wykonania i narzędzia do optymalizacji zapytań Plan ApexSQL (do którego ostatnio wpadłem).

Możesz zainstalować i zintegrować ApexSQL Plan z SQL Server Management Studio, dzięki czemu plany wykonania mogą być przeglądane bezpośrednio z SSMS.

Wyświetlanie szacunkowych planów wykonania w planie ApexSQL

1. Kliknij przycisk Nowe zapytanie w SSMS i wklej tekst zapytania w oknie tekstowym zapytania. Kliknij prawym przyciskiem myszy i wybierz opcję „Wyświetl plan wykonania” z menu kontekstowego.

2. Diagramy planu wykonania zostaną wyświetlone na karcie Plan wykonania w sekcji wyników. Następnie kliknij prawym przyciskiem myszy plan wykonania iw menu kontekstowym wybierz opcję „Otwórz w planie ApexSQL”.

3. Szacowany plan wykonania zostanie otwarty w planie ApexSQL i może być analizowany pod kątem optymalizacji zapytań.

Wyświetlanie rzeczywistych planów wykonania w planie ApexSQL

Aby wyświetlić rzeczywisty plan wykonania zapytania, przejdź do drugiego kroku wspomnianego wcześniej, ale teraz, gdy zostanie wyświetlony plan szacunkowy, kliknij przycisk „Rzeczywisty” na głównym pasku wstążki w planie ApexSQL.

Po kliknięciu przycisku „Rzeczywiste” zostanie wyświetlony Rzeczywisty plan wykonania wraz ze szczegółowym podglądem parametrów kosztu wraz z innymi danymi planu wykonania.

Więcej informacji na temat przeglądania planów wykonania można znaleźć, klikając ten link .

Moim ulubionym narzędziem do uzyskiwania i dogłębnej analizy planów wykonania zapytań jest SQL Sentry Plan Explorer . Jest o wiele bardziej przyjazny dla użytkownika, wygodny i kompleksowy do szczegółowej analizy i wizualizacji planów wykonania niż SSMS.

Oto przykładowy zrzut ekranu, aby dowiedzieć się, jakie funkcje oferuje narzędzie:

To tylko jeden z widoków dostępnych w narzędziu. Zwróć uwagę na zestaw zakładek u dołu okna aplikacji, który pozwala uzyskać różne typy reprezentacji planu wykonania oraz przydatne dodatkowe informacje.

Ponadto nie zauważyłem żadnych ograniczeń jego darmowej wersji, które uniemożliwiają codzienne korzystanie z niej lub zmuszają do zakupu wersji Pro. Jeśli więc wolisz pozostać przy wersji bezpłatnej, nic nie zabrania ci tego.

AKTUALIZACJA: (Dzięki Martinowi Smithowi ) Plan Explorer jest teraz bezpłatny! Widzieć informacje znaleźć na stronie http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view .

Plany zapytań można uzyskać z sesji zdarzeń rozszerzonych za pośrednictwem query_post_execution_showplanzdarzenia. Oto przykładowa sesja XEvent:

/*
    Generated via "Query Detail Tracking" template.
*/
CREATE EVENT SESSION [GetExecutionPlan] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)),

/* Remove any of the following events (or include additional events) as desired. */
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.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT 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)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT 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.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(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.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_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.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_statement_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.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))) 
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

Po utworzeniu sesji (w SSMS) przejdź do Eksploratora obiektów i zagłęb się w Zarządzanie | Rozszerzone wydarzenia | Sesje Kliknij sesję „GetExecutionPlan” prawym przyciskiem myszy i uruchom ją. Kliknij ponownie prawym przyciskiem myszy i wybierz „Oglądaj dane na żywo”.

Następnie otwórz nowe okno zapytania i uruchom jedno lub więcej zapytań. Oto jeden dla AdventureWorks:

USE AdventureWorks;
GO

SELECT p.Name AS ProductName, 
    NonDiscountSales = (OrderQty * UnitPrice),
    Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p 
INNER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID 
ORDER BY ProductName DESC;
GO

Po chwili powinieneś zobaczyć wyniki w zakładce „GetExecutionPlan: Live Data”. Kliknij jedno ze zdarzeń query_post_execution_showplan w siatce, a następnie kliknij kartę „Plan zapytań” pod siatką. Powinno to wyglądać podobnie do tego:

EDYCJA : Kod XEvent i zrzut ekranu zostały wygenerowane z SQL / SSMS 2012 w / SP2. Jeśli używasz SQL 2008 / R2, to może być w stanie dostosować skrypt aby go uruchomić. Ale ta wersja nie ma GUI, więc musisz wyodrębnić XML showplan, zapisać go jako plik * .sqlplan i otworzyć w SSMS. To kłopotliwe. XEvent nie istniał w SQL 2005 lub wcześniejszym. Tak więc, jeśli nie korzystasz z SQL 2012 lub nowszej, zdecydowanie sugeruję jedną z innych odpowiedzi opublikowanych tutaj.

Począwszy od SQL Server 2016+, wprowadzono funkcję Query Store do monitorowania wydajności. Zapewnia wgląd w wybór i wydajność planu zapytań. Nie jest to całkowita zamiana zdarzeń śledzenia lub zdarzeń rozszerzonych, ale ponieważ ewoluuje od wersji do wersji, możemy uzyskać w pełni funkcjonalny magazyn zapytań w przyszłych wersjach programu SQL Server. Główny przepływ w magazynie zapytań

1. Istniejące komponenty SQL Server współdziałają ze składnicą zapytań przy użyciu Menedżera magazynu zapytań.
2. Menedżer magazynu zapytań określa, którego sklepu należy użyć, a następnie przekazuje wykonanie do tego sklepu (statystyki planu lub środowiska wykonawczego lub statystyki oczekiwania na kwerendę)
   • Plan Store - utrwalanie informacji o planie wykonania
   • Magazyn statystyk wykonawczych - utrwalanie informacji o statystykach wykonania
   • Query Wait Stats Store - Trwałe informacje o statystykach oczekiwania.
3. Plan, Runtime Stats i Wait store używają Query Store jako rozszerzenia SQL Server.

1. Włączanie Query Store : Query Store działa na poziomie bazy danych na serwerze.

   • Magazyn zapytań jest domyślnie nieaktywny dla nowych baz danych.
   • Nie można włączyć magazynu zapytań dla wzorca lub tempdbbazy danych.
   • Dostępne DMV

     sys.database_query_store_options (Transact-SQL)

2. Zbieraj informacje w magazynie zapytań : Zbieramy wszystkie dostępne informacje z trzech sklepów za pomocą Query Store DMV (widoki zarządzania danymi).

   • Magazyn planów zapytań: utrwalanie informacji o planie wykonania i jest odpowiedzialny za przechwytywanie wszystkich informacji związanych z kompilacją zapytań.

     sys.query_store_query(Transact-SQL) sys.query_store_plan(Transact-SQL) sys.query_store_query_text(Transact-SQL)

   • Magazyn statystyk wykonawczych: Przechowywanie informacji o statystykach wykonania i jest to prawdopodobnie najczęściej aktualizowany sklep. Te statystyki reprezentują dane wykonania zapytania.

     sys.query_store_runtime_stats (Transact-SQL)

   • Query Wait Stats Store: Utrwalanie i przechwytywanie informacji o statystykach oczekiwania.

     sys.query_store_wait_stats (Transact-SQL)

UWAGA: Query Wait Stats Store jest dostępny tylko w SQL Server 2017+

Podobnie jak w SQL Server Management Studio (już wyjaśnione), jest to również możliwe z Datagrip, jak wyjaśniono tutaj .

1. Kliknij prawym przyciskiem myszy instrukcję SQL i wybierz Wyjaśnij plan.
2. W okienku danych wyjściowych kliknij opcję Plan.
3. Domyślnie wyświetlana jest reprezentacja drzewa zapytania. Aby zobaczyć plan zapytań, kliknij ikonę Pokaż wizualizację lub naciśnij Ctrl + Shift + Alt + U

Oto jedna ważna rzecz, którą należy wiedzieć, oprócz wszystkiego, co powiedziano wcześniej.

Plany zapytań są często zbyt skomplikowane, aby mogły być reprezentowane przez wbudowany typ kolumny XML, który ma ograniczenie do 127 poziomów zagnieżdżonych elementów. Jest to jeden z powodów, dla których sys.dm_exec_query_plan może zwrócić NULLlub nawet zgłosić błąd we wcześniejszych wersjach MS SQL, więc ogólnie bezpieczniej jest używać zamiast tego sys.dm_exec_text_query_plan . Ta ostatnia ma również przydatną funkcję bonusową polegającą na wyborze planu dla konkretnego zestawienia, a nie całej partii. Oto, w jaki sposób używasz go do przeglądania planów dla aktualnie uruchomionych instrukcji:

SELECT p.query_plan
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_text_query_plan(
                r.plan_handle,
                r.statement_start_offset,
                r.statement_end_offset) AS p

Kolumna tekstowa w tabeli wynikowej nie jest jednak bardzo przydatna w porównaniu z kolumną XML. Aby móc kliknąć wynik, który zostanie otwarty w osobnej zakładce jako diagram, bez konieczności zapisywania jego zawartości do pliku, możesz skorzystać z małej sztuczki (pamiętaj, że nie możesz po prostu użyć CAST(... AS XML)), chociaż będzie to działać tylko dla jeden rząd:

SELECT Tag = 1, Parent = NULL, [ShowPlanXML!1!!XMLTEXT] = query_plan
FROM sys.dm_exec_text_query_plan(
                -- set these variables or copy values
                -- from the results of the above query
                @plan_handle,
                @statement_start_offset,
                @statement_end_offset)
FOR XML EXPLICIT

Możesz to również zrobić za pomocą programu PowerShell, używając SET STATISTICS XML ON, aby uzyskać aktualny plan. Napisałem to tak, aby łączyło plany z wieloma oświadczeniami w jeden plan;

    ########## BEGIN : SCRIPT VARIABLES #####################
    [string]$server = '.\MySQLServer'
    [string]$database = 'MyDatabase'
    [string]$sqlCommand = 'EXEC sp_ExampleSproc'
    [string]$XMLOutputFileName = 'sp_ExampleSproc'
    [string]$XMLOutputPath = 'C:\SQLDumps\ActualPlans\'
    ########## END   : SCRIPT VARIABLES #####################

    #Set up connection
    $connectionString = "Persist Security Info=False;Integrated Security=true;Connection Timeout=0;Initial Catalog=$database;Server=$server"
    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)

    #Set up commands
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $command.CommandTimeout = 0
    $commandXMLActPlanOn = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML ON",$connection)
    $commandXMLActPlanOff = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML OFF",$connection)

    $connection.Open()

    #Enable session XML plan
    $result = $commandXMLActPlanOn.ExecuteNonQuery()

    #Execute SP and return resultsets into a dataset
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    #Set up output file name and path
    [string]$fileNameDateStamp = get-date -f yyyyMMdd_HHmmss
    [string]$XMLOutputFilePath = "$XMLOutputPath$XMLOutputFileName`_$fileNameDateStamp.sqlplan"

    #Pull XML plans out of dataset and merge into one multi-statement plan
    [int]$cntr = 1
    ForEach($table in $dataset.Tables)
    {
            if($table.Columns[0].ColumnName -eq "Microsoft SQL Server 2005 XML Showplan")
            {

                [string]$fullXMLPlan = $Table.rows[0]."Microsoft SQL Server 2005 XML Showplan"

                if($cntr -eq 1)
                    {

                    [regex]$rx = "\<ShowPlanXML xmlns\=.{1,}\<Statements\>"
                    [string]$startXMLPlan = $rx.Match($fullXMLPlan).Value
                    [regex]$rx = "\<\/Statements\>.{1,}\<\/ShowPlanXML\>"
                    [string]$endXMLPlan = $rx.Match($fullXMLPlan).Value

                    $startXMLPlan | out-file -Append -FilePath $XMLOutputFilePath

                    }

                [regex]$rx = "\<StmtSimple.{1,}\<\/StmtSimple\>"
                [string]$bodyXMLPlan = $rx.Match($fullXMLPlan).Value

                $bodyXMLPlan | out-file -Append -FilePath $XMLOutputFilePath

                $cntr += 1
            } 
    }

    $endXMLPlan | out-file -Append -FilePath $XMLOutputFilePath

    #Disable session XML plan
    $result = $commandXMLActPlanOff.ExecuteNonQuery()

    $connection.Close()

Jak wyjaśniłem w tym artykule , istnieją dwa typy planów wykonania, które można uzyskać, korzystając z programu SQL Server.

Szacowany plan wykonania

Szacowany plan wykonania jest generowany przez Optymalizator bez uruchamiania zapytania SQL.

Aby uzyskać szacunkowy plan wykonania, musisz włączyć SHOWPLAN_ALL ustawienie przed wykonaniem zapytania.

ZESTAW SHOWPLAN_ALL WŁĄCZONY

Teraz podczas wykonywania następującego zapytania SQL:

SELECT p.id
FROM post p
WHERE EXISTS (
  SELECT 1
  FROM post_comment pc
  WHERE
    pc.post_id = p.id AND
    pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

SQL Server wygeneruje następujący szacowany plan wykonania:

| NodeId | Parent | LogicalOp            | EstimateRows | EstimateIO  | EstimateCPU | AvgRowSize | TotalSubtreeCost | EstimateExecutions |
|--------|--------|----------------------|--------------|-------------|-------------|------------|------------------|--------------------|
| 1      | 0      | NULL                 | 10           | NULL        | NULL        | NULL       | 0.03374284       | NULL               |
| 2      | 1      | Top                  | 10           | 0           | 3.00E-06    | 15         | 0.03374284       | 1                  |
| 4      | 2      | Distinct Sort        | 30           | 0.01126126  | 0.000504114 | 146        | 0.03373984       | 1                  |
| 5      | 4      | Inner Join           | 46.698       | 0           | 0.00017974  | 146        | 0.02197446       | 1                  |
| 6      | 5      | Clustered Index Scan | 43           | 0.004606482 | 0.0007543   | 31         | 0.005360782      | 1                  |
| 7      | 5      | Clustered Index Seek | 1            | 0.003125    | 0.0001581   | 146        | 0.0161733        | 43                 |

Po uruchomieniu zapytania jesteśmy zainteresowani uzyskaniem szacunkowego planu wykonania, należy go wyłączyć, SHOWPLAN_ALLponieważ w przeciwnym razie bieżąca sesja bazy danych wygeneruje tylko szacowany plan wykonania zamiast wykonywania dostarczonych zapytań SQL.

SET SHOWPLAN_ALL OFF

Szacowany plan programu SQL Server Management Studio

W aplikacji SQL Server Management Studio można łatwo uzyskać szacunkowy plan wykonania dowolnego zapytania SQL, naciskając CTRL+Lskrót klawiszowy.

Rzeczywisty plan wykonania

Rzeczywisty plan wykonania SQL jest generowany przez Optymalizator podczas uruchamiania zapytania SQL. Jeśli statystyki tabeli bazy danych są dokładne, rzeczywisty plan nie powinien znacząco różnić się od szacowanego.

Aby uzyskać rzeczywisty plan wykonania na serwerze SQL, musisz włączyć STATISTICS IO, TIME, PROFILEustawienia, zgodnie z ilustracją następującego polecenia SQL:

SET STATISTICS IO, TIME, PROFILE ON

Teraz, podczas uruchamiania poprzedniego zapytania, SQL Server wygeneruje następujący plan wykonania:

| Rows | Executes | NodeId | Parent | LogicalOp            | EstimateRows | EstimateIO  | EstimateCPU | AvgRowSize | TotalSubtreeCost |
|------|----------|--------|--------|----------------------|--------------|-------------|-------------|------------|------------------|
| 10   | 1        | 1      | 0      | NULL                 | 10           | NULL        | NULL        | NULL       | 0.03338978       |
| 10   | 1        | 2      | 1      | Top                  | 1.00E+01     | 0           | 3.00E-06    | 15         | 0.03338978       |
| 30   | 1        | 4      | 2      | Distinct Sort        | 30           | 0.01126126  | 0.000478783 | 146        | 0.03338679       |
| 41   | 1        | 5      | 4      | Inner Join           | 44.362       | 0           | 0.00017138  | 146        | 0.02164674       |
| 41   | 1        | 6      | 5      | Clustered Index Scan | 41           | 0.004606482 | 0.0007521   | 31         | 0.005358581      |
| 41   | 41       | 7      | 5      | Clustered Index Seek | 1            | 0.003125    | 0.0001581   | 146        | 0.0158571        |

SQL Server parse and compile time:
   CPU time = 8 ms, elapsed time = 8 ms.

(10 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'post'. Scan count 0, logical reads 116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'post_comment'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(6 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Po uruchomieniu zapytania jesteśmy zainteresowani uzyskaniem rzeczywistego planu wykonania, musisz wyłączyć takie STATISTICS IO, TIME, PROFILE ONustawienia:

SET STATISTICS IO, TIME, PROFILE OFF

Aktualny plan programu SQL Server Management Studio

W aplikacji SQL Server Management Studio można łatwo uzyskać szacunkowy plan wykonania dowolnego zapytania SQL, naciskając CTRL+Mskrót klawiszowy.

Aby uzyskać więcej informacji na temat uzyskiwania planu wykonania podczas korzystania z programu SQL Server, zapoznaj się z tym artykułem .

Wyjaśnienie planu wykonania może być bardzo szczegółowe i zajmuje sporo czasu na czytanie, ale podsumowując, jeśli użyjesz „wyjaśnij” przed zapytaniem, powinno ono dostarczyć wielu informacji, w tym, które części zostały wykonane jako pierwsze i tak dalej. jeśli chcesz przeczytać więcej szczegółów na ten temat, skompilowałem mały blog na ten temat, który również wskazuje właściwe referencje. https://medium.com/swlh/jetbrains-datagrip-explain-plan-ac406772c470