sys.dm_exec_query_plan (Transact-SQL)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di Azure SQLDatabase SQL su Microsoft Fabric

La sys.dm_exec_query_plan funzione di gestione dinamica restituisce lo Showplan in formato XML per il lotto specificato dal handle del piano. Il piano specificato tramite l'handle di piano può essere memorizzato nella cache o in esecuzione.

Lo schema XML per Showplan viene pubblicato e disponibile in questo sito Web Microsoft. È disponibile anche nella directory in cui è installato SQL Server.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

sys.dm_exec_query_plan(plan_handle)

Arguments

plan_handle

Un token che identifica in modo univoco un piano di esecuzione di query per un batch eseguito il cui piano risiede nella cache dei piani o è attualmente in esecuzione. plan_handle è varbinary(64).

L'oggetto plan_handle può essere ottenuto dagli oggetti a gestione dinamica seguenti:

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (Transact-SQL)

• sys.dm_exec_trigger_stats (Transact-SQL)

Tabella restituita

Osservazioni:

Nelle condizioni seguenti non viene restituito alcun output Showplan nella query_plan colonna della tabella restituita per sys.dm_exec_query_plan:

• Se il piano di query specificato da using plan_handle è stato sfrattato dalla cache del piano, la query_plan colonna della tabella restituita è NULL. Ad esempio, questa condizione potrebbe verificarsi se c'è un ritardo temporale tra il momento in cui la pianta è stata catturata e quella in cui è stata utilizzata con sys.dm_exec_query_plan

• Alcune istruzioni Transact-SQL non vengono memorizzate nella cache, ad esempio istruzioni o istruzioni per operazioni bulk contenenti valori letterali stringa superiori a 8 KB. Gli Showplans XML per tali istruzioni non possono essere recuperati utilizzando sys.dm_exec_query_plan a meno che il batch non sia attualmente in esecuzione perché non esistono nella cache.

• Se un batch o una procedura stored Transact-SQL contiene una chiamata a una funzione definita dall'utente o a SQL dinamico, ad esempio usando EXEC (string), lo Showplan XML compilato per la funzione definita dall'utente non è incluso nella tabella restituita da sys.dm_exec_query_plan per la procedura batch o memorizzata. Invece, devi effettuare una chiamata separata a sys.dm_exec_query_plan per il handle del piano che corrisponde alla funzione definita dall'utente.

Quando una query ad hoc utilizza parametrizzazione semplice o forzata, la query_plan colonna conterrà solo il testo dell'istruzione e non il piano effettivo della query. Per restituire il piano della query, richiedi sys.dm_exec_query_plan il handle del piano della query parametrizzata preparata. Puoi determinare se la query è stata parametrizzata facendo riferimento alla sql colonna della vista sys.syscacheobjects o alla colonna di testo della vista sys.dm_exec_sql_text dynamic management.

Permissions

Per eseguire sys.dm_exec_query_plan, un utente deve essere membro del ruolo di sysadmin fisso server o avere il permesso VIEW SERVER STATE sul server.

Permessi per SQL Server 2022 e versioni successive

È richiesta l'autorizzazione VIEW SERVER PERFORMANCE STATE per il server.

Examples

I seguenti esempi mostrano come utilizzare la sys.dm_exec_query_plan vista di gestione dinamica.

Per visualizzare i Showplans XML, esegui le seguenti query nel Editor di query di SQL Server Management Studio, poi seleziona ShowPlanXML nella query_plan colonna della tabella restituita da sys.dm_exec_query_plan Il Programma Showplan XML visualizzato nel pannello riassuntivo di Management Studio. Per salvare il Programma di Presentazione XML in un file, clicca con il tasto destro su ShowPlanXML nella query_plan colonna, seleziona Salva Risultati come, nomi il file nel formato <file name>.sqlplan; ad esempio, MyXMLShowplan.sqlplan.

A. Recupero del piano di query memorizzato nella cache per un query o un batch Transact-SQL con esecuzione prolungata

I piani di query per vari tipi di batch Transact-SQL, ad esempio batch ad hoc, stored procedure e funzioni definite dall'utente, vengono memorizzati nella cache in un'area di memoria denominata cache dei piani. Ogni piano della query memorizzato nella cache è identificato da un ID univoco denominato handle del piano. Puoi specificare questo handle di piano con la sys.dm_exec_query_plan vista di gestione dinamica per recuperare il piano di esecuzione per una particolare query o batch Transact-SQL.

Se una query Transact-SQL o un batch viene eseguito a lungo in una determinata connessione a SQL Server, recuperare il piano di esecuzione per tale query o batch per individuare la causa del ritardo. Nell'esempio seguente viene illustrato come recuperare il piano Showplan XML per una query o un batch con esecuzione prolungata.

Per prima cosa, recupera l'ID della sessione (SPID) per il processo che sta eseguendo la query o il batch utilizzando la sp_who procedura memorizzata:

exec sp_who;  
GO  

L'insieme di risultati restituito da sp_who indica che l'ID della sessione è 54. Puoi usare l'ID sessione con la sys.dm_exec_requests vista di gestione dinamica per recuperare il handle del piano usando la seguente query:

SELECT * FROM sys.dm_exec_requests  
WHERE session_id = 54;  
GO  

La tabella restituita da sys.dm_exec_requests indica che il handle del piano per la query o batch a lentezza è 0x06000100A27E7C1FA821B10600, che puoi specificare come plan_handle argomento con sys.dm_exec_query_plan per recuperare il piano di esecuzione in formato XML come segue. Il piano di esecuzione in formato XML per la query o batch a lenta esecuzione è contenuto nella query_plan colonna della tabella restituita da sys.dm_exec_query_plan.

SELECT * 
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);  
GO  

B. Recupero di tutti i piani di query dalla cache dei piani

Per recuperare uno snapshot di tutti i piani di query disponibili nella cache dei piani, è possibile recuperare gli handle per tutti i piani di query nella cache eseguendo una query sulla vista a gestione dinamica sys.dm_exec_cached_plans. Gli handle dei piani sono archiviati nella colonna plan_handle della vista sys.dm_exec_cached_plans. È quindi necessario utilizzare l'operatore CROSS APPLY per passare gli handle dei piani a sys.dm_exec_query_plan come illustrato di seguito. L'output Showplan XML per ogni piano disponibile nella cache dei piani viene indicato nella colonna query_plan della tabella restituita.

SELECT * 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO  

C. Recupero di tutti i piani di query per cui sono state raccolte informazioni statistiche sulle query dalla cache dei piani da parte del server

Per recuperare uno snapshot di tutti i piani di query disponibili nella cache dei piani per i quali il server ha raccolto informazioni statistiche, è possibile recuperare gli handle dei piani per tutti i piani di query nella cache eseguendo una query sulla vista a gestione dinamica sys.dm_exec_query_stats. Gli handle dei piani sono archiviati nella colonna plan_handle della vista sys.dm_exec_query_stats. È quindi necessario utilizzare l'operatore CROSS APPLY per passare gli handle dei piani a sys.dm_exec_query_plan come illustrato di seguito. L'output Showplan XML per ogni piano disponibile nella cache dei piani per cui il server ha raccolto informazioni statistiche viene indicato nella colonna query_plan della tabella restituita.

SELECT * 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);  
GO  

D. Recupero di informazioni sulle prime cinque query in base al tempo medio di CPU

Nell'esempio seguente vengono restituiti i piani e il tempo medio di CPU per le prime cinque query.

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
   plan_handle, query_plan   
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)  
ORDER BY total_worker_time/execution_count DESC;  
GO  

Contenuti correlati

• Visioni e funzioni di gestione dinamica del sistema
• sys.dm_exec_cached_plans (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• sys.dm_exec_requests (Transact-SQL)
• sys.sp_who (Transact-SQL)
• Guida di riferimento per gli operatori showplan logici e fisici
• sys.dm_exec_text_query_plan (Transact-SQL)