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:

• 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)

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:

• sys.dm_exec_query_stats

• sys.dm_exec_requests

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  

Vedi anche

sys.dm_exec_query_plan (Transact-SQL)