THIS TOPIC APPLIES TO: SQL Server (starting with 2016)Azure SQL DatabaseAzure SQL Data Warehouse Parallel 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:
|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 SP1.
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 2014 SP2 and SQL Server 2016 and can be enabled:
- Globally by using trace flag 7412.
- Using the query_thread_profile extended event.
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
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.
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; 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