sys.dm_exec_query_plan (Transact-SQL)

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Restituisce il piano Showplan in formato XML per il batch specificato dall'handle di 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)  

Argomenti

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

È possibile ottenere il plan_handle 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

Nome colonna	Tipo di dati	Descrizione
dbid	smallint	ID del database di contesto attivo al momento della compilazione dell'istruzione Transact-SQL corrispondente a questo piano. Per istruzioni SQL ad hoc e preparate, l'ID del database in cui sono state compilate le istruzioni. La colonna ammette i valori Null.
objectid	int	ID dell'oggetto (ad esempio, stored procedure o funzione definita dall'utente) per il piano della query. Per i batch ad hoc e preparati, questa colonna è Null. La colonna ammette i valori Null.
number	smallint	Valore intero della stored procedure numerata. Ad esempio, un gruppo di procedure per l'applicazione ordini può essere denominato orderproc;1, orderproc;2 e così via. Per i batch ad hoc e preparati, questa colonna è Null. La colonna ammette i valori Null.
crittografato	bit	Indica se la stored procedure corrispondente è crittografata. 0 = non crittografata 1 = crittografata La colonna non ammette i valori Null.
query_plan	xml	Contiene la rappresentazione Showplan in fase di compilazione del piano di esecuzione della query specificato con plan_handle. La rappresentazione Showplan è in formato XML. Viene generato un piano per ogni batch contenente ad esempio istruzioni Transact-SQL ad hoc, chiamate di stored procedure e chiamate di funzioni definite dall'utente. La colonna ammette i valori Null.

Osservazioni:

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

• Se il piano di query specificato tramite plan_handle è stato rimosso dalla cache dei piani, la colonna query_plan della tabella restituita è Null. Ad esempio, questa condizione può verificarsi se si verifica un ritardo di tempo tra l'acquisizione dell'handle del piano e il momento in cui è stato usato 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 showplan 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 Transact-SQL o una stored procedure contiene una chiamata a una funzione definita dall'utente o una chiamata a SQL dinamico, ad esempio tramite EXEC (stringa), lo showplan XML compilato per la funzione definita dall'utente non è incluso nella tabella restituita da sys.dm_exec_query_plan per il batch o la stored procedure. È invece necessario effettuare una chiamata separata a sys.dm_exec_query_plan per l'handle di piano che corrisponde alla funzione definita dall'utente.

Quando una query ad hoc usa parametrizzazione semplice o forzata, la colonna query_plan conterrà solo il testo dell'istruzione e non il piano di query effettivo. Per restituire il piano di query, chiamare sys.dm_exec_query_plan per l'handle di piano della query con parametri preparata. È possibile determinare se la query è stata parametrizzata facendo riferimento alla colonna sql della vista sys.syscacheobjects o alla colonna di testo della sys.dm_exec_sql_text vista a gestione dinamica.

Nota

A causa di una limitazione del numero di livelli annidati consentiti nel tipo di dati xml , sys.dm_exec_query_plan non può restituire piani di query che soddisfano o superano 128 livelli di elementi annidati. Nelle versioni precedenti di SQL Server, questa condizione impediva il completamento del piano di query e generava l'errore 6335. In SQL Server 2005 (9.x) Service Pack 2 e versioni successive la colonna query_plan restituisce NULL.
È possibile usare la funzione di gestione dinamica sys.dm_exec_text_query_plan (Transact-SQL) per restituire l'output del piano di query in formato testo.

Autorizzazioni

Per eseguire sys.dm_exec_query_plan, un utente deve essere membro del ruolo predefinito del server sysadmin o disporre dell'autorizzazione VIEW SERVER STATE per il server.

Autorizzazioni per SQL Server 2022 e versioni successive

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

Esempi

Gli esempi seguenti illustrano come usare la vista a gestione dinamica sys.dm_exec_query_plan .

Per visualizzare gli showplan XML, eseguire le query seguenti nella Editor di query di SQL Server Management Studio, quindi fare clic su ShowPlanXML nella colonna query_plan della tabella restituita da sys.dm_exec_query_plan. Lo showplan XML viene visualizzato nel riquadro di riepilogo di Management Studio. Per salvare lo showplan XML in un file, fare clic con il pulsante destro del mouse su ShowPlanXML nella colonna query_plan, scegliere Salva risultati con nome, denominare il file nel formato <file_name.sqlplan>, ad esempio MyXMLShowplan.sqlplan.

R. 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. È possibile specificare questo handle di piano con la vista a gestione dinamica sys.dm_exec_query_plan per recuperare il piano di esecuzione per una determinata 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.

Nota

Per eseguire questo esempio, sostituire i valori per session_id e plan_handle con valori specifici del server.

Utilizzare innanzitutto la stored procedure sp_who per recuperare l'ID del processo server (SPID, Server Process ID) per il processo che esegue la query o il batch:

USE master;  
GO  
exec sp_who;  
GO  

Il set dei risultati restituito da sp_who indica che il valore di SPID è 54. È possibile utilizzare questo SPID con la vista a gestione dinamica sys.dm_exec_requests per recuperare l'handle del piano utilizzando la query seguente:

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

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

USE master;  
GO  
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.

USE master;  
GO  
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.

USE master;  
GO  
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  

Vedi anche

Funzioni a gestione dinamica e DMV (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sp_who (Transact-SQL)
Guida di riferimento a operatori Showplan logici e fisici
sys.dm_exec_text_query_plan (Transact-SQL)