Skip to content

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

Latest commit

 

History

History
111 lines (87 loc) · 5.14 KB

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

File metadata and controls

111 lines (87 loc) · 5.14 KB
title sys.dm_exec_query_statistics_xml (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 11/16/2016
ms.prod sql
ms.prod_service database-engine
ms.component dmv's
ms.reviewer
ms.suite sql
ms.technology system-objects
ms.tgt_pltfrm
ms.topic conceptual
f1_keywords
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_statistics_xml_TSQL
dm_exec_query_statistics_xml_TSQL
dm_exec_query_statistics_xml
helpviewer_keywords
sys.dm_exec_query_statistics_xml management view
ms.assetid fdc7659e-df41-488e-b2b5-0d79734dfecb
caps.latest.revision 6
author pmasl
ms.author pelopes
manager craigg

sys.dm_exec_query_statistics_xml (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2016-xxxx-xxxx-xxx-md]

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 [!INCLUDEssSQL15] 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 [!INCLUDEssSQL14] SP2 and [!INCLUDEssSQL15] 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 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 [!INCLUDEssManStudio], and 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 standard 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)