sys.dm_exec_query_plan

Articolo
12/15/2008

Data aggiornamento: 12 dicembre 2006

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 il piano Showplan è pubblicato e disponibile in questo sito Web Microsoft. È inoltre disponibile nella directory di installazione di SQL Server 2005 nel percorso seguente:

\\Programmi\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

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). È possibile ottenere plan_handle dagli oggetti a gestione dinamica seguenti:

sys.dm_exec_cached_plans

sys.dm_exec_query_stats

sys.dm_exec_requests

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 i batch ad hoc e preparati, questa colonna è null. La colonna supporta 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 supporta valori Null.
number	smallint	Valore integer 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 supporta valori Null.
encrypted	bit	Indica se la stored procedure corrispondente è crittografata. 0 = non crittografata 1 = crittografata La colonna non supporta 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 supporta 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 di massa 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 vista a gestione dinamica sys.dm_exec_sql_text. Per ulteriori informazioni sulla parametrizzazione, vedere Parametrizzazione semplice e Parametrizzazione forzata.

A causa della limitazione esistente per il numero di livelli nidificati consentiti per il tipo di dati xml, sys.dm_exec_query_plan non supporta 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 2005, questa condizione impedisce il completamento del piano di query e genera l'errore 6335. In Service Pack 2, la colonna query_plan restituisce NULL. È possibile utilizzare la funzione a gestione dinamica sys.dm_exec_text_query_plan 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 della query memorizzato nella cache per un query o un batch Transact-SQL con esecuzione prolungata

In SQL Server 2005, i piani di 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 il server ha raccolto informazioni statistiche sulle query dalla cache dei piani

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

Cronologia modifiche

Versione	Cronologia
12 dicembre 2006	Nuovo contenuto: Aggiunta di informazioni sul comportamento di questa funzione quando il piano di query contiene 128 o più livelli nidificati. Aggiunta di informazioni sulla restituzione del piano di query per query ad hoc con parametri. Aggiunta dell'esempio D. Contenuto modificato: Aggiunta di un chiarimento per indicare che il piano di query può essere memorizzato nella cache o in esecuzione. Modifica delle definizioni delle colonne dbid, objectid e number per indicare che le colonne restituiscono NULL per i batch ad hoc e preparati.
5 dicembre 2005	Contenuto modificato: Modifica del percorso di installazione per lo schema Showplan XML.

12 dicembre 2006

Nuovo contenuto:

Aggiunta di informazioni sul comportamento di questa funzione quando il piano di query contiene 128 o più livelli nidificati.
Aggiunta di informazioni sulla restituzione del piano di query per query ad hoc con parametri.
Aggiunta dell'esempio D.

Contenuto modificato:

Aggiunta di un chiarimento per indicare che il piano di query può essere memorizzato nella cache o in esecuzione.
Modifica delle definizioni delle colonne dbid, objectid e number per indicare che le colonne restituiscono NULL per i batch ad hoc e preparati.

5 dicembre 2005