sys.dm_exec_query_plan (Transact-SQL)
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.
L'XML Schema per il piano Showplan è pubblicato e disponibile in questo sito Web Microsoft. È inoltre disponibile nella directory di installazione di SQL Server.
.gif "Icona di collegamento a un argomento")
Convenzioni della sintassi Transact-SQL
Sintassi
sys.dm_exec_query_plan ( plan_handle )
Argomenti
plan_handle
Identifica in modo univoco un piano di query per un batch memorizzato nella cache o in esecuzione.plan_handle è di tipo varbinary(64). plan_handle può essere ottenuto dagli oggetti a gestione dinamica seguenti:
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, le procedure utilizzate per l'applicazione orders potrebbero essere denominate orderproc;1, orderproc;2 e così via. Per i batch ad hoc e preparati, questa colonna è null. La colonna ammette i valori Null. |
encrypted |
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 della 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 della query specificato tramite plan_handle è stato eliminato dalla cache dei piani, la colonna query_plan della tabella restituita è Null. Questa condizione si verifica, ad esempio, in presenza di un ritardo di tempo tra l'acquisizione dell'handle del piano e il relativo utilizzo in sys.dm_exec_query_plan.
Alcune istruzioni Transact-SQL non vengono memorizzate nella cache, ad esempio le istruzioni per operazioni bulk o le istruzioni che contengono valori letterali stringa con dimensioni maggiori di 8 KB. Non è possibile recuperare Showplan XML per tali istruzioni tramite sys.dm_exec_query_plan a meno che il batch non sia in esecuzione, perché non esistono nella cache.
Se un batch o una stored procedure Transact-SQL contiene una chiamata a una funzione definita dall'utente o una chiamata a codice SQL dinamico, ad esempio tramite EXEC (string), il piano Showplan XML compilato per la funzione definita dall'utente non viene incluso nella tabella restituita da sys.dm_exec_query_plan per il batch o la stored procedure. È invece necessario eseguire una chiamata separata a sys.dm_exec_query_plan per l'handle di piano corrispondente alla funzione definita dall'utente.
Quando una query ad hoc utilizza la 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 del piano della query con parametri preparata. È possibile determinare se è stata eseguita la parametrizzazione della query facendo riferimento alla colonna sql della vista sys.syscacheobjects o alla colonna di testo della DMV sys.dm_exec_sql_text.
A causa della limitazione esistente per il numero di livelli nidificati consentiti per il tipo di dati xml, in sys.dm_exec_query_plan non è supportata la restituzione di piani di query con un numero di livelli di elementi nidificati maggiore o uguale a 128. Nelle versioni precedenti di SQL Server questa condizione impediva il completamento del piano di query e generava l'errore 6335. In SQL Server 2005 Service Pack 2 e versioni più recenti la colonna query_plan restituisce NULL. È possibile utilizzare la funzione a 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, è necessario che l'utente sia un membro del ruolo predefinito del server sysadmin oppure che disponga dell'autorizzazione VIEW SERVER STATE nel server.
Esempi
Negli esempi seguenti viene illustrato l'utilizzo della vista a gestione dinamica sys.dm_exec_query_plan.
Per visualizzare i Showplan XML, eseguire le query seguenti nell'editor di query di SQL Server Management Studio e quindi fare clic su ShowPlanXML nella colonna query_plan della tabella restituita da sys.dm_exec_query_plan. Il piano Showplan XML verrà visualizzato nel riquadro di riepilogo di Management Studio. Per salvare il piano Showplan XML in un file, fare clic con il pulsante destro del mouse su ShowPlanXML nella colonna query_plan, scegliere Salva risultati con nome e specificare un nome per il file nel formato <file_name>.sqlplan; ad esempio ShowplanXML.sqlplan.
A.Recupero del piano di query memorizzato nella cache per un query o un batch Transact-SQL con esecuzione prolungata
I piani delle query per vari tipi di batch Transact-SQL, come batch ad hoc, stored procedure e funzioni definite dall'utente, vengono memorizzati nella cache in un'area della memoria denominata cache dei piani. Ogni piano della query memorizzato nella cache è identificato da un ID univoco denominato handle del piano. È possibile specificare l'handle del piano con la vista a gestione dinamica sys.dm_exec_query_plan per recuperare il piano di esecuzione per una query o un batch Transact-SQL specifico.
Se l'esecuzione di una query o un batch Transact-SQL risulta prolungata in una particolare connessione a SQL Server, è possibile recuperare il piano di esecuzione di tale query o batch per individuare le cause 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 di session_id e plan_handle con valori specifici del server in uso.
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 del piano per la query o il batch con esecuzione prolungata è 0x06000100A27E7C1FA821B10600, valore che è possibile specificare come argomento plan_handle per la vista sys.dm_exec_query_plan per recuperare il piano di esecuzione in formato XML come illustrato di seguito. Il piano di esecuzione in formato XML per la query o il batch con esecuzione prolungata è 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 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 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
Vedere anche
Riferimento
Funzioni e viste a gestione dinamica (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sys.dm_exec_text_query_plan (Transact-SQL)