sys.dm_exec_query_statistics_xml (Transact-SQL)sys.dm_exec_query_statistics_xml (Transact-SQL)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure SQL 数据仓库 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

返回查询执行计划正在进行的请求。Returns query execution plan for in-flight requests. 使用此 DMV 来检索 showplan XML 使用临时统计信息。Use this DMV to retrieve showplan XML with transient statistics.

语法Syntax

sys.dm_exec_query_statistics_xml(session_id)  

参数Arguments

session_idsession_id
执行要查找的批处理的会话 id。Is the session id executing the batch to be looked up. session_idsmallintsession_id is smallint. session_id可以从以下动态管理对象中获得:session_id can be obtained from the following dynamic management objects:

返回的表Table Returned

列名Column Name 数据类型Data Type 描述Description
session_idsession_id smallintsmallint 会话的 ID。ID of the session. 不可为 Null。Not nullable.
request_idrequest_id intint 请求的 ID。ID of the request. 不可为 Null。Not nullable.
sql_handlesql_handle varbinary(64)varbinary(64) 是一个令牌,用于唯一标识批处理或存储的过程的查询。Is a token that uniquely identifies the batch or stored procedure that the query is part of. 可以为 NULL。Nullable.
plan_handleplan_handle varbinary(64)varbinary(64) 是唯一标识当前正在执行的批次查询执行计划的令牌。Is a token that uniquely identifies a query execution plan for a batch that is currently executing. 可以为 NULL。Nullable.
query_planquery_plan xmlxml 包含运行时使用指定的查询执行计划的显示计划表示形式plan_handle包含部分统计信息。Contains the runtime Showplan representation of the query execution plan that is specified with plan_handle containing partial statistics. 显示计划的格式为 XML。The Showplan is in XML format. 为包含即席 Transact-SQLTransact-SQL 语句、存储过程调用以及用户定义函数调用等内容的每个批查询生成一个计划。One plan is generated for each batch that contains, for example ad hoc Transact-SQLTransact-SQL statements, stored procedure calls, and user-defined function calls. 可以为 NULL。Nullable.

备注Remarks

此系统函数是从开始提供SQL Server 2016 (13.x)SQL Server 2016 (13.x)SP1。This system function is available starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1. 请参阅知识库3190871See KB 3190871

此系统函数下都适用标准轻型查询执行统计信息分析基础结构。This system function works under both standard and lightweight query execution statistics profiling infrastructure. 有关详细信息,请参阅查询分析基础结构For more information, see Query Profiling Infrastructure.

在以下情况下不显示计划输出中返回query_plan返回的表的列sys.dm_exec_query_statistics_xml: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:

  • 对应于指定的查询计划,如果session_id不再执行query_plan列返回的表为 null。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. 例如,可能会发生此问题,如果没有捕获计划句柄的和已使用与时间之间的时间延迟sys.dm_exec_query_statistics_xmlFor example, this condition may occur if there is a time delay between when the plan handle was captured and when it was used with sys.dm_exec_query_statistics_xml.

由于在允许的嵌套级别数的限制造成xml数据类型sys.dm_exec_query_statistics_xml不能返回达到或超过 128 级的嵌套元素的查询计划。Due to a limitation in the number of nested levels allowed in the xml data type, sys.dm_exec_query_statistics_xml cannot return query plans that meet or exceed 128 levels of nested elements. 在早期版本的 SQL ServerSQL Server 中,这种情况将导致无法返回查询计划,并生成错误 6335。In earlier versions of SQL ServerSQL Server, this condition prevented the query plan from returning and generates error 6335. 在中SQL Server 2005 (9.x)SQL Server 2005 (9.x)Service Pack 2 和更高版本query_plan列返回 NULL。In SQL Server 2005 (9.x)SQL Server 2005 (9.x) Service Pack 2 and later versions, the query_plan column returns NULL.

权限Permissions

要求具有对服务器的 VIEW SERVER STATE 权限。Requires VIEW SERVER STATE permission on the server.

示例Examples

A.A. 查看实时查询计划和执行统计信息用于运行批处理Looking at live query plan and execution statistics for a running batch

下面的示例查询sys.dm_exec_requests若要查找感兴趣的查询并复制其session_id输出中。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  

然后,若要获取实时查询计划和执行统计信息,请使用复制session_id系统函数与sys.dm_exec_query_statistics_xmlThen, 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
动态管理视图和函数 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
与数据库相关的动态管理视图(Transact SQL)Database Related Dynamic Management Views (Transact-SQL)