Oprócz wyczerpującej odpowiedzi już zamieszczonej czasami przydatna jest możliwość programowego dostępu do planu wykonania w celu wyodrębnienia informacji. Przykładowy kod 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ład StartCapture Definicja

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ład StopCapture Definicja

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

• dla szacunkowy Plan zapytań możesz nacisnąć Ctrl + L lub następujący przycisk.

• dla rzeczywistego planu zapytań , możesz nacisnąć Ctrl + M lub poniższy przycisk przed wykonaniem zapytania.

• dla Live Query Plan , (tylko w SSMS 2016) użyj następującego przycisku przed wykonuję zapytanie.

Oprócz metod opisanych w poprzednich odpowiedziach, możesz również użyć darmowej przeglądarki planu wykonania i narzędzia optymalizacji zapytań ApexSQL Plan (na które ostatnio wpadłem).

Możesz zainstalować i zintegrować Plan ApexSQL z SQL Server Management Studio, dzięki czemu plany realizacji można przeglądać bezpośrednio z SSMS.

Przeglądanie szacunkowych planów realizacji w planie ApexSQL

1. kliknij przycisk New Query w SSMS i wklej tekst zapytania w okno tekstowe zapytania. Kliknij prawym przyciskiem myszy i wybierz opcję "Wyświetl szacowany plan realizacji" z menu kontekstowego.

1. diagramy planu realizacji zostaną wyświetlone na karcie Plan realizacji w sekcji wyniki. Następnie kliknij prawym przyciskiem myszy plan realizacji i w menu kontekstowym wybierz opcję "Otwórz w planie ApexSQL".

1. szacunkowy plan realizacji zostanie otwarty w planie ApexSQL i może być analizowany pod kątem optymalizacja zapytań.

Przeglądanie rzeczywistych planów realizacji w planie ApexSQL

Aby wyświetlić rzeczywisty plan wykonania zapytania, kontynuuj od 2. kroku wspomnianego wcześniej, ale teraz, po wyświetleniu szacunkowego planu, kliknij przycisk "rzeczywisty" na głównym pasku wstążki w planie ApexSQL.

Po kliknięciu przycisku "rzeczywisty" zostanie wyświetlony rzeczywisty plan realizacji ze szczegółowym podglądem kosztów parametry wraz z innymi danymi planu wykonania.

Więcej informacji na temat przeglądania planów realizacji można znaleźć pod adresem ten link .

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

Oto przykładowy zrzut ekranu, aby dowiedzieć się, jaka funkcjonalność jest oferowana przez narzędzie:

To tylko jeden z widoków dostępnych w narzędziu. Zwróć uwagę na zestaw zakładek na dole okno aplikacji, które pozwala uzyskać różne rodzaje reprezentacji planu realizacji oraz przydatne dodatkowe informacje.

Ponadto nie zauważyłem żadnych ograniczeń jego darmowej edycji, które uniemożliwiają korzystanie z niego na co dzień lub zmuszają do zakupu wersji Pro w końcu. Tak więc, jeśli wolisz trzymać się darmowej edycji, nic nie zabrania ci tego robić.

Aktualizacja: (dzięki Martin Smith ) Plan Explorer jest teraz wolny! Zobacz też http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view Po szczegóły.

Plany zapytań można uzyskać z rozszerzonej sesji zdarzeń za pomocą zdarzenia query_post_execution_showplan. 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 zdarzenia / sesje. Kliknij prawym przyciskiem myszy sesję "GetExecutionPlan" i rozpocznij ją. Kliknij prawym przyciskiem myszy ponownie i wybierz "Watch Live Data".

Następnie otwórz nowe okno zapytań 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 lub dwóch powinieneś zobaczyć niektóre wyniki w zakładce "GetExecutionPlan: dane na żywo". Kliknij jedno z zdarzeń query_post_execution_showplan w siatce, a następnie kliknij kartę "Plan zapytań" poniżej siatki. Powinno wyglądać podobnie do tego:

EDIT : kod XEvent i zrzut ekranu zostały wygenerowane z SQL / SSMS 2012 w / SP2. Jeśli używasz SQL 2008/ R2, możesz być w stanie dostosować skrypt żeby go uruchomić. Ale ta wersja nie ma GUI, więc musisz wyodrębnić XML showplan, zapisać go jako *.plik sqlplan i otwórz go w SSMS. To uciążliwe. XEvents nie istniał w SQL 2005 lub wcześniej. Tak więc, jeśli nie jesteś na SQL 2012 lub później, zdecydowanie sugeruję jedną z innych odpowiedzi zamieszczonych tutaj.

Począwszy od SQL Server 2016+ wprowadzono funkcję sklepu zapytań w celu monitorowania wydajności. Zapewnia wgląd w wybór planu zapytań i wydajność. Nie jest to całkowite zastąpienie zdarzeń trace lub extended, ale ponieważ ewoluuje z wersji na wersję, możemy uzyskać w pełni funkcjonalny magazyn zapytań w przyszłych wydaniach z SQL Server. Główny przepływ zapytania Store

1. istniejące komponenty SQL Server współdziałają ze sklepem zapytań poprzez wykorzystanie sklepu zapytań Manager.
2. Menedżer zapytań określa, który sklep ma być używany, a następnie przekazuje wykonanie do tego sklepu (Plan lub statystyki Runtime lub statystyki oczekiwania na zapytanie)
   • Plan Store-utrzymywanie informacji o planie wykonania
   • Runtime Stats Store - utrzymywanie informacji o statystykach wykonania
   • Query Wait Stats Store-utrzymujące się informacje o statystykach oczekiwania.
3. Plan, Runtime Stats I Wait store używa Query Store jako rozszerzenia do SQL Serwer.

1. Włączenie sklepu zapytań: Sklep zapytań działa na poziomie bazy danych na serwerze.

   • Query Store domyślnie nie jest aktywny dla nowych baz danych.
   • nie można włączyć magazynu zapytań dla bazy danych master lub tempdb.
   • Dostępny DMV

     sys.database_query_store_options (Transact-SQL)

2. Zbieramy informacje w sklepie zapytań : zbieramy wszystkie dostępne informacje z trzech sklepów za pomocą sklepu zapytań DMV (widoki zarządzania danymi).

   • Skład Planu Zapytania: Utrzymywanie informacji o planie wykonania i jest odpowiedzialne 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)

   • Sklep Statystyki Runtime: Uporczywe statystyki wykonania i jest to prawdopodobnie najczęściej aktualizowany sklep. Statystyki te reprezentują dane wykonania zapytania.

     sys.query_store_runtime_stats (Transact-SQL)

   • Query Wait Stats Store: Utrzymywanie 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+

Oto jedna ważna rzecz, o której warto wiedzieć, oprócz wszystkiego, co zostało powiedziane wcześniej.

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 wynikowej tabeli nie jest jednak zbyt przydatna w porównaniu z kolumną XML. Aby móc kliknąć wynik, który ma być otwarty w oddzielnej karcie jako diagram, bez konieczności zapisywania jego Zawartości w pliku, możesz użyć małej sztuczki (pamiętaj, że nie możesz użyć tylko CAST(... AS XML)), chociaż będzie to działać tylko dla jednego 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

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

1. Kliknij prawym przyciskiem myszy polecenie SQL i wybierz opcję wyjaśnij plan.
2. w okienku wyjście kliknij Plan.
3. domyślnie wyświetlana jest reprezentacja drzewa zapytania. Aby zobaczyć kliknij ikonę Pokaż wizualizację lub naciśnij Ctrl+Shift+Alt + U