sys.dm_exec_query_statistics_xml (Transact-SQL)

  • 项目

适用于： SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库Azure SQL 托管实例

返回正在进行的请求的查询执行计划。 使用此 DMV 检索具有暂时性统计信息的显示计划 XML。

语法

sys.dm_exec_query_statistics_xml(session_id)

参数

session_id
执行要查找的批处理的会话 ID。session_id 是 smallint。 session_id 可以从下列动态管理对象中获得：

返回的表

列名 数据类型 说明
session_id smallint 会话的 ID。 不可为 Null。
request_id int 请求的 ID。 不可为 Null。
sql_handle varbinary(64) 是唯一标识查询所属的批处理或存储过程的令牌。 Nullable。
plan_handle varbinary(64) 是唯一标识当前正在执行的批处理的查询执行计划的令牌。 Nullable。
query_plan xml 包含使用含有部分统计信息的 plan_handle 指定的查询执行计划的运行时显示计划表示形式。 显示计划的格式为 XML。 为包含即席 Transact-SQL 语句、存储过程调用以及用户定义函数调用等内容的每个批查询生成一个计划。 Nullable。

备注

重要

由于使用 sys.dm_exec_query_statistics_xml DMV 执行监视存储过程时可能出现随机访问冲突 (AV)，SQL Server 2017 (14.x) CU 26 和 SQL Server 2019 (15.x) CU 12 中的显示计划 XML 属性 <ParameterList>ParameterRuntimeValue 值已删除。 在对长时间运行的存储过程进行疑难解答时，此值可能很有用。

从 SQL Server 2017 (14.x) CU 31 和 SQL Server 2019 (15.x) CU 19 开始，已重新启用收集显示计划 XML 属性 <ParameterList> 值 ParameterRuntimeValue，其中包含跟踪标志 2446。 此跟踪标志可以收集运行时参数值，但会带来额外的开销。

警告

跟踪标志 2446 不应在生产环境中持续启用，而应只限于用于时间限制的疑难解答。 使用此跟踪标志将引入其他可能重要的 CPU 和内存开销，因为我们将创建一个提供运行时参数信息的 Showplan XML 片段，而无论是否调用 sys.dm_exec_query_statistics_xml DMV。

注意

从 SQL Server 2022 (16.x)、Azure SQL 数据库和 Azure SQL 托管实例开始，要在数据库级别完成此操作，请参阅 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) 中的 FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION 选项。

此系统函数自 SQL Server 2016 (13.x) SP1 起开始提供。 请参阅知识库 3190871

此系统函数适用于“标准”和“轻型”查询执行统计信息分析基础结构。 有关详细信息，请参阅查询分析基础结构。

在以下情况下，sys.dm_exec_query_statistics_xml 的返回表的 query_plan 列不会返回显示计划输出：

  • 如果与指定的 session_id 对应的查询计划不再执行，则返回的表的 query_plan 列为 null。 例如，如果在计划句柄捕获时间及其与 sys.dm_exec_query_statistics_xml 一起使用的时间之间存在时间延迟，则可能会发生此情况。

由于 xml 数据类型中允许的嵌套级别数存在限制，sys.dm_exec_query_statistics_xml 无法返回满足或超过 128 个嵌套元素级别的查询计划。 在早期版本的 SQL Server 中，这种情况将导致无法返回查询计划，并生成错误 6335。 在 SQL Server 2005 (9.x) Service Pack 2 及更高版本中，query_plan 列返回 NULL。

权限

在 SQL Server 上，需要服务器上的 VIEW SERVER STATE 权限。
在 SQL 数据库 Premium 层上，需要在数据库中拥有 VIEW DATABASE STATE 权限。 在 SQL 数据库标准层和基本层上，需要“服务器管理员”或“Microsoft Entra 管理员”帐户才能查看实时统计信息。

SQL Server 2022 及更高版本的权限

要求对服务器具有 VIEW SERVER PERFORMANCE STATE 权限。

示例

A. 查看正在运行的批处理的实时查询计划和执行统计信息

以下示例查询 sys.dm_exec_requests 以查找感兴趣的查询并从输出中复制其 session_id。

SELECT * FROM sys.dm_exec_requests;  
GO

然后，要获取实时查询计划和执行统计信息，请将复制的 session_id 与系统函数 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

或合并所有正在运行的请求。

--Run this 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 er
	CROSS APPLY sys.dm_exec_query_statistics_xml(session_id) eqs
WHERE er.session_id <> @@spid;
GO

另请参阅

跟踪标志
动态管理视图和函数 (Transact-SQL)
与数据库有关的动态管理视图 (Transact-SQL)