sys.dm_exec_text_query_plan (Transact-SQL)
Restituisce il piano Showplan in formato testo per un batch Transact-SQL o per un'istruzione specifica nel batch. Il piano di query specificato tramite l'handle del piano può essere memorizzato nella cache o in esecuzione. Questa funzione con valori di tabella è simile a sys.dm_exec_query_plan (Transact-SQL), ma con le differenze seguenti:
L'output del piano di query viene restituito in formato testo.
Per l'output del piano di query non sono previsti limiti di dimensioni.
È possibile specificare singole istruzioni nel batch.
Si applica a: SQL Server (SQL Server 2008 tramite versione corrente), Database SQL di Windows Azure (versione iniziale tramite versione corrente). |
.gif "Icona di collegamento a un argomento")
Convenzioni della sintassi Transact-SQL
Sintassi
sys.dm_exec_text_query_plan
(
plan_handle
, { statement_start_offset | 0 | DEFAULT }
, { statement_end_offset | -1 | DEFAULT }
)
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 l'handle del piano dagli oggetti a gestione dinamica seguenti:
statement_start_offset | 0 | DEFAULT
Indica, in byte, la posizione iniziale della query descritta dalla riga all'interno del testo del batch o dell'oggetto persistente. statement_start_offset è di tipo int. Il valore 0 indica l'inizio del batch. Il valore predefinito è 0.È possibile ottenere l'offset iniziale dell'istruzione dagli oggetti a gestione dinamica seguenti:
statement_end_offset | -1 | DEFAULT
Indica, in byte, la posizione finale della query descritta dalla riga all'interno del testo del batch o dell'oggetto persistente.statement_start_offset è di tipo int.
Il valore -1 indica la fine del batch. Il valore predefinito è -1.
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 |
nvarchar(max) |
Contiene la rappresentazione Showplan della fase di compilazione del piano di esecuzione della query specificato con plan_handle. La rappresentazione Showplan è in formato testo. 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 plan della tabella restituita per sys.dm_exec_text_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_text_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 per tali istruzioni tramite sys.dm_exec_text_query_plan 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_text_query_plan per il batch o la stored procedure. È invece necessario eseguire una chiamata separata a sys.dm_exec_text_query_plan per il plan_handle 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_text_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.
Autorizzazioni
Per eseguire sys.dm_exec_text_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
A.Recupero del piano della query memorizzato nella cache per un query o un batch Transact-SQL con esecuzione prolungata
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 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. Nell'esempio seguente viene restituito il piano di query per l'handle del piano specificato e vengono utilizzati i valori predefiniti 0 e -1 per restituire tutte le istruzioni nella query o nel batch.
USE master;
GO
SELECT query_plan
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);
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 DMV sys.dm_exec_cached_plans. Gli handle del piano vengono archiviati nella colonna plan_handle di sys.dm_exec_cached_plans. Utilizzare quindi l'operatore CROSS APPLY per passare gli handle dei piani a sys.dm_exec_text_query_plan come illustrato di seguito. L'output Showplan 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_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);
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 vengono archiviati nella colonna plan_handle di sys.dm_exec_query_stats. Utilizzare quindi l'operatore CROSS APPLY per passare gli handle dei piani a sys.dm_exec_text_query_plan come illustrato di seguito. L'output Showplan per ogni piano 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_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);
GO
D.Recupero di informazioni sulle prime cinque query in base al tempo medio di CPU
Nell'esempio seguente vengono restituiti i piani di query e il tempo medio di CPU per le prime cinque query. La funzione sys.dm_exec_text_query_plan specifica i valori predefiniti 0 e -1 per restituire tutte le istruzioni nel batch nel piano di 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_text_query_plan(qs.plan_handle, 0, -1)
ORDER BY total_worker_time/execution_count DESC;
GO