sys.dm_exec_query_plan_stats (Transact-SQL)

Articolo
03/13/2024

Si applica a: SQL Server 2029 (15.x) Database Azure SQL Istanza gestita di SQL di Azure

Restituisce l'equivalente dell'ultimo piano di esecuzione effettivo noto per un piano di query memorizzato nella cache in precedenza.

Sintassi

sys.dm_exec_query_plan_stats ( plan_handle )

Argomenti

plan_handle

Un token che identifica in modo univoco un piano di esecuzione di query per un batch eseguito il cui 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:

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	xml	Contiene l'ultima rappresentazione showplan di runtime nota del piano di esecuzione effettivo 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 ammette i valori Null.

Osservazioni:

È una funzionalità che prevede il consenso esplicito. Per abilitare a livello di server, usare il flag di traccia 2451. Per l'abilitazione a livello di database, utilizzare l'opzione LAST_QUERY_PLAN_STATS in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Questa funzione di sistema funziona nell'infrastruttura di profilatura leggera delle statistiche di esecuzione delle query. Per altre informazioni, vedere Infrastruttura di profilatura query.

L'output Showplan di sys.dm_exec_query_plan_stats contiene le informazioni seguenti:

Tutte le informazioni in fase di compilazione trovate nel piano memorizzato nella cache
Informazioni di runtime, ad esempio il numero effettivo di righe per operatore, il tempo totale della CPU della query e il tempo di esecuzione, gli avvisi di spill, il dop effettivo, la memoria massima usata e la memoria concessa

Nelle condizioni seguenti viene restituito un output Showplan equivalente a un piano di esecuzione effettivo nella query_plan colonna della tabella restituita per sys.dm_exec_query_plan_stats:

Il piano è disponibile in sys.dm_exec_cached_plans.

AND
La query eseguita è complessa o richiede risorse.

Nelle seguenti condizioni, viene restituito un output Showplan semplificato¹ nella colonna query_plan della tabella restituita per sys.dm_exec_query_plan_stats:

Il piano è disponibile in sys.dm_exec_cached_plans.

AND
La query è abbastanza semplice, in genere categorizzata come parte di un carico di lavoro OLTP.

¹ Fa riferimento a un showplan che contiene solo l'operatore del nodo radice (SELECT).

Nelle condizioni seguenti non viene restituito alcun output da sys.dm_exec_query_plan_stats:

Il piano di query specificato tramite plan_handle è stato rimosso dalla cache dei piani.

OPPURE
Il piano di query non era memorizzabile nella cache. Per ulteriori informazioni, vedere memorizzazione nella cache e riutilizzo del piano di esecuzione.

Nota

Una limitazione nel numero di livelli annidati consentiti nel tipo di dati xml significa che sys.dm_exec_query_plan non può restituire piani di query che soddisfano o superano 128 livelli di elementi annidati. Nelle versioni precedenti di SQL Server, questa condizione impediva il completamento del piano di query e generava l'errore 6335. In SQL Server 2005 (9.x) Service Pack 2 e versioni successive la colonna query_plan restituisce NULL.

Autorizzazioni

È richiesta l'autorizzazione VIEW SERVER STATE per il server.

Autorizzazioni per SQL Server 2022 e versioni successive

È richiesta l'autorizzazione VIEW SERVER PERFORMANCE STATE per il server.

Esempi

R. Esaminare l'ultimo piano di esecuzione effettivo delle query noto per un piano specifico memorizzato nella cache

Nell'esempio seguente viene eseguita una query su sys.dm_exec_cached_plans per trovare la il piano specifico e copiare plan_handle dall'output.

SELECT * FROM sys.dm_exec_cached_plans;
GO

Quindi, per ottenere l'ultimo piano di esecuzione di query effettivo noto, usare il copiato plan_handle con la funzione di sistema sys.dm_exec_query_plan_stats.

SELECT * FROM sys.dm_exec_query_plan_stats(< copied plan_handle >);
GO

B. Guarda l'ultimo piano di esecuzione delle query effettivo noto per tutti i piani memorizzati nella cache

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;
GO

C. Esaminare l'ultimo piano di esecuzione della query effettivo noto per un piano memorizzato nella cache specifico e il testo della query

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps
WHERE st.text LIKE 'SELECT * FROM Person.Person%';
GO

D. Esaminare gli eventi memorizzati nella cache per il trigger

SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle)
WHERE objtype ='Trigger';
GO