sql management Come ottengo un Query Execution Plan?




execution plan sql server (8)

Come con SQL Server Management Studio (già spiegato), è anche possibile con Datagrip come spiegato here .

  1. Fare clic con il tasto destro del mouse su un'istruzione SQL e selezionare Spiega piano.
  2. Nel riquadro Output, fare clic su Plan.
  3. Per impostazione predefinita, viene visualizzata la rappresentazione ad albero della query. Per visualizzare il piano di query, fai clic sull'icona Mostra visualizzazione o premi Ctrl + Maiusc + Alt + U

In Microsoft SQL Server come posso ottenere un piano di esecuzione della query per una query / stored procedure?


Answer #1

Ecco una cosa importante da sapere oltre a quanto detto prima.

I piani di query sono spesso troppo complessi per essere rappresentati dal tipo di colonna XML incorporato che ha una limitazione di 127 livelli di elementi nidificati. Questo è uno dei motivi per cui sys.dm_exec_query_plan può restituire NULL o anche lanciare un errore nelle versioni precedenti di MS SQL, quindi in genere è più sicuro usare sys.dm_exec_text_query_plan . Quest'ultimo ha anche una utile funzione di bonus di selezionare un piano per una particolare affermazione piuttosto che l'intero batch. Ecco come lo usi per visualizzare i piani per le dichiarazioni attualmente in esecuzione:

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

La colonna di testo nella tabella risultante non è tuttavia molto utile rispetto a una colonna XML. Per poter fare clic sul risultato da aprire in una scheda separata come un diagramma, senza dover salvare il suo contenuto in un file, puoi usare un piccolo trucco (ricorda che non puoi usare solo CAST(... AS XML) ) , anche se funzionerà solo per una singola riga:

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

Answer #2

A partire da SQL Server 2016+, è stata introdotta funzionalità di Query Store per monitorare le prestazioni. Fornisce informazioni sulla scelta e sulle prestazioni del piano di query. Non si tratta di una sostituzione completa di eventi di tracciamento o di eventi estesi, ma dal momento che si evolve da una versione all'altra, potremmo ottenere un archivio di query completamente funzionale nelle versioni future di SQL Server. Il flusso principale di Query Store

  1. I componenti esistenti di SQL Server interagiscono con l'archivio query utilizzando Gestione archivi query.
  2. Query Store Manager determina quale Store deve essere utilizzato e quindi passa l'esecuzione a quell'archivio (Plan o Runtime Stats o Query Wait Stats)
    • Pianificare Memorizzare - Persistere le informazioni del piano di esecuzione
    • Runtime Stats Store - Persistenza delle informazioni statistiche di esecuzione
    • Memorizzazione delle statistiche di attesa delle query - Informazioni sulle statistiche di attesa persistenti.
  3. Plan, Runtime Stats e Wait store utilizzano Query Store come un'estensione di SQL Server.

  1. Abilitazione di Query Store : Query Store funziona a livello di database sul server.

    • Query Store non è attivo per i nuovi database per impostazione predefinita.
    • Non è possibile abilitare l'archivio query per il database master o tempdb .
    • DMV disponibile

      sys.database_query_store_options (Transact-SQL)

  2. Raccogli informazioni nel Query Store : raccogliamo tutte le informazioni disponibili dai tre negozi utilizzando Query Store DMV (Data Management Views).

    • Memorizza piano di query: persiste le informazioni del piano di esecuzione ed è responsabile dell'acquisizione di tutte le informazioni correlate alla compilazione di query.

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

    • Runtime Stats Store: persiste le informazioni sulle statistiche di esecuzione ed è probabilmente lo store più frequentemente aggiornato. Queste statistiche rappresentano i dati di esecuzione della query.

      sys.query_store_runtime_stats (Transact-SQL)

    • Memorizzazione delle statistiche di attesa delle query: persistenza e acquisizione delle informazioni sulle statistiche di attesa.

      sys.query_store_wait_stats (Transact-SQL)

NOTA: l' archivio Query Wait Stats è disponibile solo in SQL Server 2017+


Answer #3

I piani di query possono essere ottenuti da una sessione di eventi estesi tramite l'evento query_post_execution_showplan . Ecco una sessione XEvent di esempio:

/*
    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

Dopo aver creato la sessione, (in SSMS), andare in Esplora oggetti e scorrere in Gestione | Eventi estesi | Sessions. Fare clic con il tasto destro sulla sessione "GetExecutionPlan" e avviarla. Fare di nuovo clic con il tasto destro e selezionare "Guarda dati in tempo reale".

Quindi, apri una nuova finestra di query ed esegui una o più query. Ecco uno per 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

Dopo un momento o due, dovresti vedere alcuni risultati nella scheda "GetExecutionPlan: Live Data". Fai clic su uno degli eventi query_post_execution_showplan nella griglia, quindi fai clic sulla scheda "Piano di query" sotto la griglia. Dovrebbe assomigliare a questo:

EDIT : il codice XEvent e lo screenshot sono stati generati da SQL / SSMS 2012 w / SP2. Se stai usando SQL 2008 / R2, potresti essere in grado di modificare lo script per farlo funzionare. Ma quella versione non ha una GUI, quindi dovresti estrarre lo showplan XML, salvarlo come un file * .sqlplan e aprirlo in SSMS. È ingombrante. XEvents non esisteva in SQL 2005 o versioni precedenti. Quindi, se non sei su SQL 2012 o successivo, ti suggerirei vivamente una delle altre risposte pubblicate qui.


Answer #4

Esistono diversi metodi per ottenere un piano di esecuzione, che uno da utilizzare dipenderà dalle circostanze. In genere è possibile utilizzare SQL Server Management Studio per ottenere un piano, tuttavia se per qualche motivo non è possibile eseguire la query in SQL Server Management Studio, potrebbe essere utile essere in grado di ottenere un piano tramite SQL Server Profiler o ispezionando la cache del piano.

Metodo 1: utilizzo di SQL Server Management Studio

SQL Server è dotato di un paio di funzionalità intuitive che rendono molto semplice l'acquisizione di un piano di esecuzione, semplicemente assicurati che la voce di menu "Includi piano di esecuzione effettivo" (presente nel menu "Query") sia spuntata ed esegui la query come normale .

Se si sta tentando di ottenere il piano di esecuzione per le istruzioni in una stored procedure, è necessario eseguire la stored procedure, in questo modo:

exec p_Example 42

Una volta completata la tua query, dovresti vedere una scheda aggiuntiva intitolata "Piano di esecuzione" nel riquadro dei risultati. Se hai eseguito molte affermazioni, potresti vedere molti piani visualizzati in questa scheda.

Da qui è possibile ispezionare il piano di esecuzione in SQL Server Management Studio oppure fare clic con il tasto destro del mouse sul piano e selezionare "Salva piano di esecuzione con ..." per salvare il piano in un file in formato XML.

Metodo 2 - Utilizzo delle opzioni SHOWPLAN

Questo metodo è molto simile al metodo 1 (in effetti questo è ciò che SQL Server Management Studio esegue internamente), tuttavia l'ho incluso per completezza o se non è disponibile SQL Server Management Studio.

Prima di eseguire la query, eseguire una delle seguenti dichiarazioni. La dichiarazione deve essere l'unica dichiarazione nel batch, ovvero non è possibile eseguire un'altra istruzione allo stesso tempo:

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

Queste sono opzioni di connessione e quindi è necessario eseguirlo solo una volta per connessione. A partire da questo momento, tutte le istruzioni eseguite verranno accompagnate da un set di risultati aggiuntivo contenente il piano di esecuzione nel formato desiderato: è sufficiente eseguire la query come si farebbe normalmente per visualizzare il piano.

Una volta che hai finito puoi disattivare questa opzione con la seguente dichiarazione:

SET <<option>> OFF

Confronto dei formati del piano di esecuzione

A meno che tu non abbia una forte preferenza, la mia raccomandazione è di usare l'opzione STATISTICS XML . Questa opzione è equivalente all'opzione "Includi piano di esecuzione effettiva" in SQL Server Management Studio e fornisce la maggior parte delle informazioni nel formato più conveniente.

  • SHOWPLAN_TEXT - Visualizza un piano di esecuzione stimato basato sul testo base, senza eseguire la query
  • SHOWPLAN_ALL - Visualizza un piano di esecuzione stimato basato su testo con stime dei costi, senza eseguire la query
  • SHOWPLAN_XML - Visualizza un piano di esecuzione stimato basato su XML con stime dei costi, senza eseguire la query. Ciò equivale all'opzione "Visualizza piano di esecuzione stimato ..." in SQL Server Management Studio.
  • STATISTICS PROFILE - Esegue la query e visualizza un piano di esecuzione effettivo basato sul testo.
  • STATISTICS XML - Esegue la query e visualizza un piano di esecuzione effettivo basato su XML. Ciò è equivalente all'opzione "Includi piano di esecuzione effettiva" in SQL Server Management Studio.

Metodo 3 - Utilizzo di SQL Server Profiler

Se non è possibile eseguire direttamente la query (o la query non viene eseguita lentamente quando viene eseguita direttamente - ricordare che vogliamo un piano di esecuzione della query non valido), è possibile acquisire un piano utilizzando una traccia di SQL Server Profiler. L'idea è di eseguire la query mentre è in esecuzione una traccia che sta catturando uno degli eventi "Showplan".

Si noti che, in base al carico, è possibile utilizzare questo metodo in un ambiente di produzione, tuttavia è necessario prestare attenzione. I meccanismi di definizione dei profili di SQL Server sono progettati per ridurre al minimo l'impatto sul database, ma ciò non significa che non ci sarà alcun impatto sulle prestazioni. Potresti anche avere problemi a filtrare e identificare il piano corretto nella tua traccia se il tuo database è in uso intensivo. Dovresti ovviamente controllare con il tuo DBA per vedere se sono contenti di averlo fatto sul loro prezioso database!

  1. Aprire SQL Server Profiler e creare una nuova traccia collegandosi al database desiderato rispetto al quale si desidera registrare la traccia.
  2. Nella scheda "Selezione eventi" selezionare "Mostra tutti gli eventi", selezionare la riga "Prestazioni" -> "Showplan XML" ed eseguire la traccia.
  3. Mentre la traccia è in esecuzione, fare tutto ciò che è necessario per eseguire la query a esecuzione lenta.
  4. Attendi il completamento della query e interrompi la traccia.
  5. Per salvare la traccia, fare clic con il tasto destro del mouse su xml del piano in SQL Server Profiler e selezionare "Estrai dati evento ..." per salvare il piano in file in formato XML.

Il piano che ottieni è equivalente all'opzione "Includi piano di esecuzione effettiva" in SQL Server Management Studio.

Metodo 4 - Ispezione della cache delle query

Se non è possibile eseguire direttamente la query e non è possibile acquisire una traccia di profiler, è comunque possibile ottenere un piano stimato ispezionando la cache del piano di query SQL.

DMVs la cache del piano interrogando i DMVs SQL Server. La seguente è una query di base che elencherà tutti i piani di query memorizzati nella cache (come xml) insieme al loro testo SQL. Nella maggior parte dei database sarà necessario aggiungere ulteriori clausole di filtro per filtrare i risultati fino ai soli piani a cui si è interessati.

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)

Esegui questa query e fai clic sul piano XML per aprire il piano in una nuova finestra - fai clic con il pulsante destro e seleziona "Salva piano di esecuzione come ..." per salvare il piano in file in formato XML.

Gli appunti:

Poiché ci sono così tanti fattori coinvolti (che vanno dalla tabella e dallo schema dell'indice fino ai dati archiviati e le statistiche della tabella) dovresti sempre cercare di ottenere un piano di esecuzione dal database che ti interessa (normalmente quello che sta vivendo un rendimento problema).

Non è possibile acquisire un piano di esecuzione per le stored procedure crittografate.

piani di esecuzione "effettivi" o "stimati"

Un piano di esecuzione effettivo è uno in cui SQL Server esegue effettivamente la query, mentre un piano di esecuzione stimato SQL Server elabora ciò che farebbe senza eseguire la query. Sebbene logicamente equivalente, un piano di esecuzione reale è molto più utile in quanto contiene ulteriori dettagli e statistiche su ciò che effettivamente è accaduto durante l'esecuzione della query. Questo è essenziale quando si diagnosticano problemi in cui le stime di SQL Server non sono attive (ad esempio quando le statistiche non sono aggiornate).

Come interpreto un piano di esecuzione della query?

Questo è un argomento abbastanza degno di un book (gratuito) a sé stante.

Guarda anche:


Answer #5

Oltre ai metodi descritti nelle risposte precedenti, è anche possibile utilizzare un programma di visualizzazione di piani di esecuzione gratuiti e uno strumento di ottimizzazione delle query ApexSQL Plan (su cui mi sono imbattuto recentemente).

È possibile installare e integrare il piano ApexSQL in SQL Server Management Studio, in modo tale che i piani di esecuzione possano essere visualizzati direttamente da SSMS.

Visualizzazione dei piani di esecuzione stimati nel piano ApexSQL

  1. Fare clic sul pulsante Nuova query in SSMS e incollare il testo della query nella finestra di testo della query. Fare clic con il tasto destro e selezionare l'opzione "Visualizza piano di esecuzione stimato" dal menu di scelta rapida.

  1. I diagrammi del piano di esecuzione mostreranno la scheda Piano di esecuzione nella sezione dei risultati. Fare clic con il tasto destro del mouse sul piano di esecuzione e nel menu di scelta rapida selezionare l'opzione "Apri in piano ApexSQL".

  1. Il piano di esecuzione stimato verrà aperto nel piano ApexSQL e può essere analizzato per l'ottimizzazione delle query.

Visualizzazione dei piani di esecuzione effettivi nel piano ApexSQL

Per visualizzare il piano di esecuzione effettivo di una query, continuare dal secondo passaggio menzionato in precedenza, ma ora, una volta visualizzato il piano stimato, fare clic sul pulsante "Attuale" dalla barra a nastro principale in Piano ApexSQL.

Una volta cliccato il pulsante "Attuale", il piano di esecuzione effettivo verrà mostrato con un'anteprima dettagliata dei parametri di costo insieme ad altri dati del piano di esecuzione.

Ulteriori informazioni sulla visualizzazione dei piani di esecuzione possono essere trovate seguendo questo link .


Answer #6

Il mio strumento preferito per ottenere e analizzare in profondità i piani di esecuzione delle query è SQL Sentry Plan Explorer . È molto più user-friendly, conveniente e completo per l'analisi dettagliata e la visualizzazione dei piani di esecuzione rispetto a SSMS.

Ecco una schermata campione per avere un'idea di quale funzionalità è offerta dallo strumento:

È solo una delle viste disponibili nello strumento. Nota una serie di schede nella parte inferiore della finestra dell'app, che ti consente di ottenere diversi tipi di rappresentazione del piano di esecuzione e utili informazioni aggiuntive.

Inoltre, non ho notato alcuna limitazione della sua edizione gratuita che impedisce di usarlo su base giornaliera o ti costringe ad acquistare la versione Pro alla fine. Quindi, se preferisci attenersi alla versione gratuita, nulla ti impedisce di farlo.

AGGIORNAMENTO: (Grazie a Martin Smith ) Plan Explorer ora è gratis! Vedi http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view per i dettagli.


Answer #7

Oltre alla risposta completa già pubblicata a volte è utile poter accedere al piano di esecuzione in modo programmatico per estrarre informazioni. Il codice di esempio per questo è sotto.

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

Esempio di definizione StartCapture

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

Esempio di definizione di StopCapture

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




sql-execution-plan