sys.dm_exec_text_query_plan (Transact-SQL)
Si applica a:
SQL Server database SQL di Azure Istanza gestita di SQL di Azure
Restituisce l'oggetto Showplan in formato testo per un batch Transact-SQL o per un'istruzione specifica all'interno del batch. Il piano di query specificato dall'handle di piano può essere memorizzato nella cache o attualmente in esecuzione. Questa funzione con valori di tabella è simile a sys.dm_exec_query_plan (Transact-SQL), ma presenta 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 (10.0.x) e versioni successive), database SQL di Azure.
Convenzioni relative alla 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
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:
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 è 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 è 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, 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 | nvarchar(max) | Contiene la rappresentazione Showplan in 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 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 di piano e il momento in cui è stato usato con sys.dm_exec_text_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 per tali istruzioni non possono essere recuperati tramite sys.dm_exec_text_query_plan 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_text_query_plan per il batch o la stored procedure. È invece necessario effettuare una chiamata separata a sys.dm_exec_text_query_plan per il plan_handle 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_text_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.
Autorizzazioni
Per eseguire sys.dm_exec_text_query_plan, un utente deve essere membro del ruolo predefinito del server sysadmin o disporre dell'autorizzazione VIEW SERVER STATE nel server.
Autorizzazioni per SQL Server 2022 e versioni successive
È richiesta l'autorizzazione VIEW SERVER PERFORMANCE STATE per il server.
Esempi
R. Recupero del piano della query memorizzato nella cache per un query o un batch Transact-SQL con esecuzione prolungata
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 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. 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 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_text_query_plan come illustrato di seguito. L'output Showplan per ogni piano attualmente presente nella cache dei piani si trova nella query_plan colonna 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 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_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