sys.dm_exec_query_statistics_xml (Transact-SQL)

APPLIES TO: yesSQL Server (starting with 2016) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

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




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.


This system function is available starting with SQL Server 2016 (13.x) SP1. See KB 3190871

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

Standard statistics profiling infrastructure can be enabled by using:

Lightweight statistics profiling infrastructure is available in SQL Server 2016 (13.x) SP1 and can be enabled:


Once enabled by trace flag 7412, lightweight profiling will be enabled to any consumer of the query execution statistics profiling infrastructure instead of standard profiling, such as the DMV sys.dm_exec_query_profiles. However, standard profiling is still used for SET STATISTICS XML, Include Actual Plan action in Management Studio, and query_post_execution_showplan xEvent.


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


Requires VIEW SERVER STATE permission on the server.


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;  

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 >);  

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);  

See Also

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