Skip to content

Latest commit

 

History

History
132 lines (97 loc) · 7.74 KB

sys-dm-exec-query-statistics-xml-transact-sql.md

File metadata and controls

132 lines (97 loc) · 7.74 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sys.dm_exec_query_statistics_xml (Transact-SQL)
sys.dm_exec_query_statistics_xml returns query execution plan for in-flight requests. Use this DMV to retrieve showplan XML with transient statistics.
rwestMSFT
randolphwest
03/31/2025
sql
system-objects
conceptual
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_statistics_xml_TSQL
dm_exec_query_statistics_xml_TSQL
dm_exec_query_statistics_xml
sys.dm_exec_query_statistics_xml management view
TSQL

sys.dm_exec_query_statistics_xml (Transact-SQL)

[!INCLUDE sqlserver2016-asdb-asdbmi]

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

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) A token that uniquely identifies the batch or stored procedure that the query is part of. Nullable.
plan_handle varbinary(64) A token that uniquely identifies a query execution plan for a batch that is currently executing. Nullable.
query_plan xml Contains the runtime Showplan representation of the query execution plan that is specified with plan_handle containing partial statistics. The Showplan is in XML format. One plan is generated for each batch that contains, for example ad hoc [!INCLUDE tsql] statements, stored procedure calls, and user-defined function calls. Nullable.

Limitations

Owing to a possible random access violation (AV) while executing a monitoring stored procedure with the sys.dm_exec_query_statistics_xml DMV, the Showplan XML attribute <ParameterList> value ParameterRuntimeValue was removed in [!INCLUDE ssSQL17] CU 26 and [!INCLUDE sql-server-2019] CU 12. This value could be useful while troubleshooting long running stored procedures. You can re-enable this value in [!INCLUDE ssSQL17] CU 31, [!INCLUDE sql-server-2019] CU 19, and later versions, using Trace Flag 2446. This trace flag enables the collection of the runtime parameter value at the cost of introducing extra overhead.

Caution

Trace Flag 2446 isn't meant to be enabled continuously in a production environment, but only for time-limited troubleshooting purposes. Using this trace flag introduces extra and possibly significant CPU and memory overhead, as it creates a Showplan XML fragment with runtime parameter information, whether the sys.dm_exec_query_statistics_xml DMV is called or not.

In [!INCLUDE ssSQL22], [!INCLUDE Azure SQL Database], and [!INCLUDE Azure SQL Managed Instance], you can accomplish the same functionality at the database level using the FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Remarks

This system function is available starting with [!INCLUDE sssql16-md] with Service Pack 1. For more information, see KB 3190871.

This system function works under both standard and lightweight query execution statistics profiling infrastructure. For more information, see Query Profiling Infrastructure.

Under the following conditions, no Showplan output is returned in the query_plan column of the returned table for sys.dm_exec_query_statistics_xml:

  • If the query plan that corresponds to the specified session_id is no longer executing, the query_plan column of the returned table is null. For example, this condition might occur if there's a time delay between when the plan handle was captured and when it was used with sys.dm_exec_query_statistics_xml

Due to a limitation in the number of nested levels allowed in the xml data type, sys.dm_exec_query_statistics_xml can't return query plans that meet or exceed 128 levels of nested elements. In earlier versions of [!INCLUDE ssNoVersion], this condition prevented the query plan from returning and generates error 6335. In [!INCLUDE ssVersion2005] Service Pack 2 and later versions, the query_plan column returns NULL.

Permissions

Requires VIEW SERVER STATE permission on the server, in [!INCLUDE sssql19-md] and earlier versions.

Requires VIEW SERVER PERFORMANCE STATE permission on the server, in [!INCLUDE sssql22-md] and later versions.

Requires the VIEW DATABASE STATE permission in the database, on [!INCLUDE ssSDS] Premium Tiers.

Requires the Server admin or a Microsoft Entra admin account on [!INCLUDE ssSDS] Standard and Basic Tiers.

Examples

A. Look 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 query 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 query 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 AS er
CROSS APPLY sys.dm_exec_query_statistics_xml(session_id) AS eqs
WHERE er.session_id <> @@SPID;
GO

Related content