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

适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance

返回正在进行的请求的查询执行计划。Returns query execution plan for in-flight requests. 使用此 DMV 检索带有暂时性统计信息的显示计划 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_idsession_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

从 SP1 开始,此系统函数可用 SQL Server 2016 (13.x)SQL Server 2016 (13.x)This system function is available starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1. 请参阅 KB 3190871See KB 3190871

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

在以下条件下,在 sys.dm_exec_query_statistics_xml 的返回表的 query_plan 列中不返回显示计划输出: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_xml 一起使用时存在时间延迟,则可能会出现这种情况。For 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

在上 SQL ServerSQL Server ,需要对 VIEW SERVER STATE 服务器的权限。On SQL ServerSQL Server, requires VIEW SERVER STATE permission on the server.
SQL 数据库SQL Database 高级层上,需要具有 VIEW DATABASE STATE 数据库中的权限。On SQL 数据库SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. SQL 数据库SQL Database 标准层和基本层上,需要 服务器管理员Azure Active Directory 管理员 帐户。On SQL 数据库SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

示例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)