Esporta (0) Stampa
Espandi tutto
Espandi Riduci a icona

sys.dm_exec_text_query_plan

Aggiornamento: aprile 2011

Questo argomento è OBSOLETO. La versione più recente è disponibile in Guida di riferimento a Transact-SQL 14.

ImportantImportante
Argomento non aggiornato. Per la versione corrente, vedere sys.dm_exec_text_query_plan.

Restituisce lo showplan in formato di 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, 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.

Convenzioni della sintassi Transact-SQL


sys.dm_exec_text_query_plan 
( 
    plan_handle 
    , { statement_start_offset | 0 | DEFAULT }
        , { statement_end_offset | -1 | DEFAULT }
)

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:

sys.dm_exec_cached_plans

sys.dm_exec_query_stats

sys.dm_exec_requests

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:

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 è di tipo int.

Il valore -1 indica la fine del batch. Il valore predefinito è -1.

 

Nome della 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.

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.

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.

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.

noteNota
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

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

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 del piano 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

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

Mostra:
© 2014 Microsoft