sys.dm_exec_query_statistics_xml (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns query execution plan for in-flight requests. Use this DMV to retrieve showplan XML with transient statistics.

Syntax

sys.dm_exec_query_statistics_xml(session_id)  

Arguments

session_id
Is the session id executing the batch to be looked up. session_id is smallint. session_id can be obtained from the following dynamic management objects:

Table Returned

Column Name Data Type Description
session_id smallint ID of the session. Not nullable.
request_id int ID of the request. Not nullable.
sql_handle varbinary(64) Hash map of SQL text of the request. Nullable.
plan_handle varbinary(64) Hash map of query plan. Nullable.
query_plan xml Showplan XML with partial statistics. Nullable.

Remarks

This system function is available starting with SQL Server 2016 SP1.

This system function works under both legacy and lightweight query execution statistics profiling infrastructure.

Legacy statistics profiling infrastructure can be enabled by using:

Lightweight statistics profiling infrastructure is available in SQL Server 2014 SP2 and SQL Server 2016 and can be enabled:

Note

Once enabled by trace flag 7412, lightweight profiling will be enabled to any consumer of the query execution statistics profiling infrastructure instead of legacy profiling, such as the DMV sys.dm_exec_query_profiles or query_post_execution_showplan xEvent.

Important

In TPC-C like workload tests, enabling the lightweight statistics profiling infrastructure adds a 1.5 to 2 percent overhead. In contrast, the legacy statistics profiling infrastructure can add up to 90 percent overhead for the same workload scenario.

Permissions

Requires VIEW SERVER STATE permission on the server.

Examples

A. Looking at live query plan and execution statistics for a running batch

The following example queries sys.dm_exec_requests to find the interesting query and copy its session_id from the output.

SELECT * FROM sys.dm_exec_requests;  
GO  

Then, to obtain the live query plan and execution statistics, use the copied session_id with system function 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  

Or combined for all running requests.

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

See Also

Trace Flags
Dynamic Management Views and Functions (Transact-SQL)
Database Related Dynamic Management Views (Transact-SQL)