通过

sys.dm_exec_query_optimizer_info (Transact-SQL)

  • 项目

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)

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

注意

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

名称 数据类型 描述
counter nvarchar(4000) 优化器统计信息事件的名称。
occurrence bigint 此计数器的优化事件的发生次数。
value float 每次发生事件的平均属性值。
pdw_node_id int 此分发所在节点的标识符。

适用于：Azure Synapse Analytics、Analytics Platform System (PDW)

权限

SQL Server 2019 （15.x） 和早期版本以及Azure SQL 托管实例需要VIEW SERVER STATE权限。

SQL Server 2022 （16.x） 及更高版本需要 VIEW SERVER PERFORMANCE STATE 对服务器具有权限。

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

注解

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

计数器 出现次数 值
optimizations 总优化次数。 不适用
elapsed time 总优化次数。 每次优化单个语句（查询）所用的平均时间（秒）。
final cost 总优化次数。 优化计划的平均估计开销，以内部开销单位为单位。
trivial plan 仅供内部使用 仅供内部使用
tasks 仅供内部使用 仅供内部使用
no plan 仅供内部使用 仅供内部使用
search 0 仅供内部使用 仅供内部使用
search 0 time 仅供内部使用 仅供内部使用
search 0 tasks 仅供内部使用 仅供内部使用
search 1 仅供内部使用 仅供内部使用
search 1 time 仅供内部使用 仅供内部使用
search 1 tasks 仅供内部使用 仅供内部使用
search 2 仅供内部使用 仅供内部使用
search 2 time 仅供内部使用 仅供内部使用
search 2 tasks 仅供内部使用 仅供内部使用
gain stage 0 to stage 1 仅供内部使用 仅供内部使用
gain stage 1 to stage 2 仅供内部使用 仅供内部使用
timeout 仅供内部使用 仅供内部使用
memory limit exceeded 仅供内部使用 仅供内部使用
insert stmt 语句 INSERT 的优化数。 不适用
delete stmt 语句 DELETE 的优化数。 不适用
update stmt 语句 UPDATE 的优化数。 不适用
contains subquery 包含至少一个子查询的查询的优化数。 不适用
unnest failed 仅供内部使用 仅供内部使用
tables 总优化次数。 每个优化查询引用的平均表数。
hints 指定某些提示的次数。 计数的提示包括：JOIN、查询 GROUPUNION FORCE ORDER提示、FORCE PLAN设置选项和联接提示。 不适用
order hint 强制使用联接顺序的次数。 此计数器不限于 FORCE ORDER 提示。 在查询中指定联接算法（如 an INNER HASH JOIN）也会强制联接顺序，从而递增计数器。 不适用
join hint 联接提示强制联接算法的次数。 查询 FORCE ORDER 提示不会递增此计数器。 不适用
view reference 查询中引用视图的次数。 不适用
remote query 查询引用了至少一个远程数据源的优化次数，例如具有四部分名称或结果的 OPENROWSET 表。 不适用
maximum DOP 总优化次数。 优化计划的平均有效 MAXDOP 值。 默认情况下，有效 MAXDOP 由 最大并行度 服务器配置选项确定，并且可以通过查询提示的值 MAXDOP 替代特定查询。
maximum recursion level 使用 MAXRECURSION 查询提示指定级别大于 0 的优化数。 优化中的平均 MAXRECURSION 级别，其中使用查询提示指定了最大递归级别。
indexed views loaded 仅供内部使用 仅供内部使用
indexed views matched 匹配一个或多个索引视图的优化数。 匹配的平均视图数。
indexed views used 在输出计划中使用经过匹配的一个或多个索引视图的优化次数。 使用的平均视图数。
indexed views updated 生成维护一个或多个索引视图的计划的 DML 语句的优化次数。 维护的平均视图数。
dynamic cursor request 在其中指定动态游标请求的优化数。 不适用
fast forward cursor request 指定快速向前游标请求的优化数。 不适用
merge 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;

E. 在优化期间查看提示总数

当作为查询提示包含在内时 FORCE ORDER ，会计算多少个提示？

-- Check hint count before query execution
SELECT ISNULL('', 0) AS [Before],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);

SELECT poh.PurchaseOrderID,
    poh.OrderDate,
    pod.ProductID,
    pod.DueDate,
    poh.VendorID
FROM Purchasing.PurchaseOrderHeader AS poh
INNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod
    ON poh.PurchaseOrderID = pod.PurchaseOrderID
OPTION (
    FORCE ORDER,
    RECOMPILE
);

-- check hint count after query execution
SELECT ISNULL('', 0) AS [After],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);

相关内容