sys.dm_exec_query_statistics_xml (Transact-SQL)

Articolo
03/13/2024

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

Restituisce il piano di esecuzione delle query per le richieste di distribuzione in anteprima. Utilizzare questa DMV per recuperare 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 ammette i valori NULL.
sql_handle	varbinary(64)	È un token che identifica in modo univoco il batch o la stored procedure di cui fa parte la query. Ammette valori Null.
plan_handle	varbinary(64)	È un token che identifica in modo univoco un piano di esecuzione della query per un batch attualmente in esecuzione. Ammette valori Null.
query_plan	xml	Contiene la rappresentazione Showplan di runtime del piano di esecuzione della query specificato con plan_handle contenente statistiche parziali. 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. Ammette valori Null.

Osservazioni:

Importante

A causa di una possibile violazione di accesso casuale (AV) durante l'esecuzione di una procedura memorizzata di monitoraggio con DMV sys.dm_exec_query_statistics_xml, il valore <ParameterList> dell'attributo XML Showplan 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.

A partire da SQL Server 2017 (14.x) CU 31 e SQL Server 2019 (15.x) CU 19, la raccolta del valore <ParameterList>ParameterRuntimeValue dell'attributo XML Showplan è stata riabilitata con l'inclusione del flag di traccia 2446. Questo flag di traccia abilita la raccolta del valore del parametro di runtime a costo di introdurre un sovraccarico aggiuntivo.

Avviso

il flag di traccia 2446 non è progettato per essere abilitato in modo continuo in un ambiente di produzione, ma solo per finalità di risoluzione dei problemi con limite di tempo. L'uso di questo flag di traccia introdurrà un sovraccarico aggiuntivo e probabilmente significativo di CPU e memoria perché verrà creato un frammento XML di showplan con informazioni sui parametri di runtime, indipendentemente dal fatto che la vista DMV sys.dm_exec_query_statistics_xml venga chiamata o meno.

Nota

A partire da SQL Server 2022 (16.x), database SQL di Azure e Istanza gestita di SQL di Azure, per eseguire questa operazione a livello di database, vedere l'opzione FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION in ALTER DATABA edizione Standard SCOPED CONFIGURATION (Transact-SQL).

Questa funzione di sistema è disponibile solo a partire da SQL Server 2016 (13.x) SP1. 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 altre informazioni, vedere Infrastruttura di profilatura query.

Nelle condizioni seguenti non viene restituito alcun output Showplan nella colonna query_plan 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 colonna query_plan della tabella restituita è Null. Ad esempio, questa condizione può verificarsi se si verifica un ritardo di tempo tra quando l'handle di piano è stato acquisito e quando è stato usato con sys.dm_exec_query_statistics_xml.

A causa di una limitazione nel numero di livelli nidificati 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 nidificati. 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

In SQL Server è richiesta l'autorizzazione VIEW SERVER STATE sul server.
Nei livelli database SQL Premium è necessaria l'autorizzazione VIEW DATABASE STATE nel database. In database SQL livelli Standard e Basic, richiede l'amministratore del server o un account amministratore di Microsoft Entra.

Autorizzazioni per SQL Server 2022 e versioni successive

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

Esempi

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

Nell'esempio seguente viene eseguita una query su sys.dm_exec_requests per trovare la query specifica e copiare la sua 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 il copiato session_id con la funzione di sistema sys.dm_exec_query_statistics_xml.

--Run this in a different session than the session in which your query is running.
SELECT * FROM sys.dm_exec_query_statistics_xml(< copied session_id >);  
GO

Oppure combinato per tutte le richieste in esecuzione.

--Run this in a different session than the session in which your query is running.
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 er
	CROSS APPLY sys.dm_exec_query_statistics_xml(session_id) eqs
WHERE er.session_id <> @@spid;
GO

Vedi anche

Flag di traccia
Viste e funzioni a gestione dinamica (Transact-SQL)
Viste a gestione dinamica relative ai database (DMV)(Transact-SQL)