sys.dm_exec_query_optimizer_info (Transact-SQL)

适用于:SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

返回有关 SQL Server 查询优化器操作的详细统计信息。 在优化工作负荷以确定查询优化的问题或改进之处时,可以使用此视图。 例如,可以用总优化次数、占用时间值以及最终开销值来对当前工作负荷的查询优化和优化过程中发现的任何变化进行比较。 某些计数器提供仅与 SQL Server 内部诊断使用相关的数据。 这些计数器标记为“仅供内部使用”。

注意

若要从 Azure Synapse Analytics 或 Analytics 平台系统(PDW)调用此名称,请使用名称 sys.dm_pdw_nodes_exec_query_optimizer_info。 Azure Synapse Analytics 中的无服务器 SQL 池不支持此语法。

名称 数据类型 描述
计数器 nvarchar(4000) 优化器统计信息事件的名称。
occurrence bigint 此计数器的优化事件的发生次数。
value float 每次发生事件的平均属性值。
pdw_node_id int 适用于:Azure Synapse Analytics、Analytics Platform System (PDW)

此分发所在节点的标识符。

权限

对于 SQL Server 和 SQL 托管实例,需要 VIEW SERVER STATE 权限。

在SQL 数据库基本、S0S1 服务目标以及弹性池中的数据库、服务器管理员帐户、Microsoft Entra 管理员帐户或服务器角色的成员##MS_ServerStateReader##身份是必需的。 对于所有其他 SQL 数据库服务目标,需要数据库的 VIEW DATABASE STATE 权限或 ##MS_ServerStateReader## 服务器角色中的成员身份。

SQL Server 2022 及更高版本的权限

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

备注

sys.dm_exec_query_optimizer_info 包含以下属性(计数器)。 出现的所有值将累积并在系统重新启动时设置为 0。 系统重新启动时,值字段的所有值都设置为 NULL。 指定平均值的所有“值-列”的值使用同一行中的出现次数值作为计算平均值的分母。 当 SQL Server 确定对dm_exec_query_optimizer_info更改(包括用户和系统生成的查询)时,将测量所有查询优化。 执行已缓存的计划不会更改dm_exec_query_optimizer_info中的值,只有优化非常重要。

计数器 出现次数
优化 总优化次数。 不适用
占用时间 总优化次数。 每次优化单个语句(查询)所用的平均时间(秒)。
最终开销 总优化次数。 优化计划的平均估计开销,以内部开销单位为单位。
细微计划 仅供内部使用 仅供内部使用
任务 仅供内部使用 仅供内部使用
无计划 仅供内部使用 仅供内部使用
搜索 0 仅供内部使用 仅供内部使用
搜索 0 时间 仅供内部使用 仅供内部使用
搜索 0 任务 仅供内部使用 仅供内部使用
搜索 1 仅供内部使用 仅供内部使用
搜索 1 时间 仅供内部使用 仅供内部使用
搜索 1 任务 仅供内部使用 仅供内部使用
搜索 2 仅供内部使用 仅供内部使用
搜索 2 时间 仅供内部使用 仅供内部使用
搜索 2 任务 仅供内部使用 仅供内部使用
阶段 0 到阶段 1 的收益 仅供内部使用 仅供内部使用
阶段 1 到阶段 2 的收益 仅供内部使用 仅供内部使用
timeout 仅供内部使用 仅供内部使用
超过内存限制 仅供内部使用 仅供内部使用
插入语句 用于 INSERT 语句的优化数。 不适用
删除语句 用于 DELETE 语句的优化数。 不适用
更新语句 用于 UPDATE 语句的优化数。 不适用
包含子查询 包含至少一个子查询的查询的优化数。 不适用
取消嵌套失败 仅供内部使用 仅供内部使用
总优化次数。 每个优化查询引用的平均表数。
提示 指定某些提示的次数。 计数的提示包括:JOIN、GROUP、UNION 和 FORCE ORDER 查询提示,FORCE PLAN 设置选项和联接提示。 不适用
排序提示 指定强制排序提示的次数。 不适用
联接提示 联接提示强制联接算法的次数。 不适用
视图引用 查询中引用视图的次数。 不适用
远程查询 查询至少引用一个远程数据源(例如,具有四部分名称或 OPENROWSET 结果的表)的优化数。 不适用
最大 DOP 总优化次数。 优化计划的平均有效 MAXDOP 值。 默认情况下,有效的 MAXDOP 由 最大并行度 服务器配置选项确定,并且可以通过 MAXDOP 查询提示的值替代特定查询。
最小递归级别 已用查询提示指定 MAXRECURSION 级别大于 0 的优化次数。 已用查询提示指定最大递归级别的优化中的平均 MAXRECURSION 级别。
加载的索引视图 仅供内部使用 仅供内部使用
匹配的索引视图 已匹配一个或多个索引视图的优化数。 匹配的平均视图数。
使用的索引视图 在输出计划中使用经过匹配的一个或多个索引视图的优化次数。 使用的平均视图数。
更新的索引视图 生成维护一个或多个索引视图的计划的 DML 语句的优化次数。 维护的平均视图数。
动态游标请求 已指定动态游标请求的优化次数。 不适用
快进游标请求 已指定快进游标请求的优化次数。 不适用
合并 stmt 用于 MERGE 语句的优化数。 不适用

示例

A. 查看有关优化器执行的统计信息

此 SQL Server 实例的当前优化器执行统计信息是什么?

SELECT * FROM sys.dm_exec_query_optimizer_info;  

B. 查看优化总数

执行了多少次优化?

SELECT occurrence AS Optimizations FROM sys.dm_exec_query_optimizer_info  
WHERE counter = 'optimizations';  

°C 每次优化占用的平均时间

每次优化占用的平均时间是多少?

SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization  
FROM sys.dm_exec_query_optimizer_info WHERE counter = 'elapsed time';  

D. 涉及子查询的优化部分

已优化查询中有多少包含子查询?

SELECT (SELECT CAST (occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'contains subquery') /  
       (SELECT CAST (occurrence AS float)   
        FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations')  
        AS ContainsSubqueryFraction;  

另请参阅

动态管理视图和函数 (Transact-SQL)
与执行有关的动态管理视图和函数 (Transact-SQL)