sys.dm_exec_query_statistics_xml (Transact-SQL)

Articolo
2025-04-04

Si applica a: SQL Server 2016 (13.x) e versioni successive Database SQL di Azure Istanza gestita di SQL di Azure

Restituisce il piano di esecuzione delle query per le richieste in corso. Utilizzare questa DMV per recuperare lo showplan XML con statistiche temporanee.

Sintassi

sys.dm_exec_query_statistics_xml(session_id)

Argomenti

session_id

ID sessione che esegue il batch da cercare. session_id è smallint. session_id Può essere ricavato dagli oggetti a gestione dinamica seguenti:

Tabella restituita

Nome colonna	Tipo di dati	Descrizione
`session_id`	smallint	ID della sessione. Non ammette i valori NULL.
`request_id`	int	ID della richiesta. Non nullabile.
`sql_handle`	varbinary(64)	Token che identifica in modo univoco il batch o la procedura memorizzata di cui fa parte la query. Nullable.
`plan_handle`	varbinary(64)	Token che identifica in modo univoco un piano di esecuzione di query per un batch che è attualmente in esecuzione. Nullabile.
`query_plan`	xml	Contiene la rappresentazione Showplan di runtime del piano di esecuzione della query, specificato con `plan_handle` che contiene statistiche parziali. Il 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. Annullabile.

Limitazioni

A causa di una possibile violazione di accesso casuale (AV) durante l'esecuzione di una stored procedure di monitoraggio con il DMV sys.dm_exec_query_statistics_xml, il valore dell'attributo Showplan XML <ParameterList>ParameterRuntimeValue è stato rimosso in SQL Server 2017 (14.x) CU 26 e SQL Server 2019 (15.x) CU 12. Questo valore può essere utile durante la risoluzione dei problemi relativi alle stored procedure a esecuzione prolungata. È possibile riabilitare questo valore in SQL Server 2017 (14.x) CU 31, SQL Server 2019 (15.x) CU 19 e versioni successive, usando il flag di traccia 2446. Questo flag di traccia abilita la raccolta del valore del parametro di runtime a costo di introdurre un sovraccarico aggiuntivo.

Attenzione

Il flag traccia 2446 non è progettato per essere abilitato continuamente in un ambiente di produzione, ma solo per fini di risoluzione dei problemi a tempo determinato. L'uso di questo flag di traccia introduce un sovraccarico di CPU e memoria aggiuntivo e possibilmente significativo, perché crea un frammento XML Showplan con informazioni sui parametri di runtime, indipendentemente dal fatto che la sys.dm_exec_query_statistics_xml DMV venga chiamata o meno.

In SQL Server 2022 (16.x), database SQL di Azure e Istanza gestita di SQL di Azure è possibile eseguire la stessa funzionalità a livello di database usando l'opzione FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Osservazioni:

Questa funzione di sistema è disponibile a partire da SQL Server 2016 (13.x) con Service Pack 1. Per altre informazioni, vedere kb 3190871.

Questa funzione di sistema funziona sia con l'infrastruttura di profilazione delle statistiche di esecuzione delle query standard che con quella leggera. Per ulteriori informazioni, vedere Infrastruttura di profilazione delle query.

Nelle condizioni seguenti non viene restituito alcun output Showplan nella query_plan colonna della tabella restituita per sys.dm_exec_query_statistics_xml:

Se il piano di query che corrisponde al session_id specificato non è più in esecuzione, la query_plan colonna della tabella restituita è Null. Ad esempio, questa condizione può verificarsi se si verifica un ritardo di tempo tra l'acquisizione dell'handle del piano e il momento in cui è stato usato con sys.dm_exec_query_statistics_xml

A causa di una limitazione del numero di livelli annidati consentiti nel tipo di dati xml , sys.dm_exec_query_statistics_xml 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 query_plan colonna restituisce NULL.

Autorizzazioni

È richiesta VIEW SERVER STATE l'autorizzazione per il server, in SQL Server 2019 (15.x) e versioni precedenti.

È richiesta VIEW SERVER PERFORMANCE STATE l'autorizzazione per il server, in SQL Server 2022 (16.x) e versioni successive.

Richiede l'autorizzazione VIEW DATABASE STATE nel database, nei livelli Premium del database SQL.

Richiede l'amministratore del server o un account amministratore di Microsoft Entra nei livelli Standard e Basic del database SQL.

Esempi

A. Esaminare il piano di query in tempo reale e le statistiche di esecuzione di un batch in esecuzione

Nell'esempio seguente viene sys.dm_exec_requests eseguita una query per trovare la query interessante e copiarla session_id dall'output.

SELECT *
FROM sys.dm_exec_requests;
GO

Quindi, per ottenere il piano di query in tempo reale e le statistiche di esecuzione, usare la copia session_id con la funzione sys.dm_exec_query_statistics_xml di sistema. Eseguire questa query in una sessione diversa rispetto alla sessione in cui è in esecuzione la query.

SELECT * FROM sys.dm_exec_query_statistics_xml(< copied session_id >);
GO

In alternativa, combinato per tutte le richieste in esecuzione. Esegui questa query in una sessione diversa da quella in cui la tua query è in esecuzione.

SELECT eqs.query_plan,
       er.session_id,
       er.request_id,
       er.database_id,
       er.start_time,
       er.[status],
       er.wait_type,
       er.wait_resource,
       er.last_wait_type,
       (er.cpu_time / 1000) AS cpu_time_sec,
       (er.total_elapsed_time / 1000) / 60 AS elapsed_time_minutes,
       (er.logical_reads * 8) / 1024 AS logical_reads_KB,
       er.granted_query_memory,
       er.dop,
       er.row_count,
       er.query_hash,
       er.query_plan_hash
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_query_statistics_xml(session_id) AS eqs
WHERE er.session_id <> @@SPID;
GO

Condividi tramite