通过

查询存储的优化计划强制执行

  • 项目

适用于： SQL Server 2022 (16.x) Azure SQL 数据库

查询优化是生成“足够好”查询执行计划的多阶段过程。 在某些情况下，查询编译作为查询优化的一部分，可能占整个查询执行时间的很大比例，并消耗大量系统资源。 优化计划强制执行是智能查询处理系列功能的一部分。 优化计划强制减少了重复强制查询的编译开销，并要求启用查询存储并处于 read write 模式。 生成查询执行计划后，将会存储特定编译步骤以作为优化重播脚本重复使用。 在查询存储中，优化重播脚本作为压缩的显示计划 XML 的一部分存储在隐藏属性 OptimizationReplay 中。

优化计划强制执行的实现

当查询首次完成编译过程时，基于优化所用的时间估计（基于查询优化器输入树）的阈值确定是否创建了优化重播脚本。

编译完成后，可使用多个运行时指标来评估以前的估计是否正确。 如果数据库引擎确认阈值已超过，则优化重播脚本有资格暂留。 这些运行时指标包括访问的对象数、联接数、优化期间执行的优化任务数以及实际优化时间。

使用优化重播脚本的潜在好处还会与存储优化重播脚本的开销进行比较。 与执行正常优化过程所用的时间进行比较，估计重播优化脚本的相对时间。 此估计基于优化重播脚本中存储的优化任务数，以及正常编译期间执行的优化任务数。 如果重播优化重播脚本在减少编译时间方面显示出实质性的好处，那么该优化重播脚本将会保留。

注意事项

启用优化计划强制执行功能后，优化计划强制执行的资格条件为：

  1. 只有经过完全优化的查询计划才符合条件，这可通过查看是否存在 StatementOptmLevel="FULL" 属性进行验证。

  2. 具有 RECOMPILE 提示和分布式查询的语句不符合条件。

但是，如果查询存储独立捕获由优化计划强制限定范围的查询计划，则会为同一查询的第二次重新编译创建优化重播脚本，但受默认重新编译事件的约束。 在重新编译执行计划中详细了解重新编译。

即使生成了优化重播脚本，如果查询存储配置的捕获策略条件未满足，它可能不会保留在查询存储中，尤其是该语句的执行次数及其累积的编译和执行时间。 在这种情况下，将异步从内存中删除无效的优化重播脚本。

启用和禁用优化计划强制执行

可以对数据库启用或禁用优化计划强制执行。 为数据库启用优化计划强制时，可以使用查询提示为单个查询 DISABLE_OPTIMIZED_PLAN_FORCING 禁用它。 还可以对查询存储强制的查询计划禁用优化计划强制。

对数据库启用或禁用优化计划强制执行

对于 SQL Server 2022 (16.x) 及更高版本中创建的新数据库，默认启用优化计划强制执行。 必须对使用优化计划强制执行的每个数据库启用查询存储。 升级后，具有现有数据库或从较低版本的 SQL Server 还原的数据库，默认已优化计划强制启用。

要在数据库级别启用优化计划强制执行，请使用 ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON 数据库范围配置。 如果尚未启用查询存储，必须将其启用。 在示例 A 中查找示例代码，或在使用查询存储监视性能中详细了解查询存储。

要在数据库级别禁用优化计划强制执行，请使用 ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF 数据库范围配置。

使用查询提示禁用优化计划强制执行

如果数据库中启用了优化计划强制执行功能，你可使用 DISABLE_OPTIMIZED_PLAN_FORCING 查询提示对单个查询禁用优化计划强制执行。

在示例 E 中查找应用此查询提示的示例。

使用查询存储强制执行计划，但禁用优化计划强制执行

sp_query_store_force_plan 过程包含一个 disable_optimized_plan_forcing 参数。 若要使用此参数，存储过程需要 sp_query_store_force_plan 额外的参数。 将调用 @replica_group_id额外的参数。 默认情况下，主 @replica_group_id 副本的值为 1（1），即使在没有配置的次要副本的情况下也是如此。

查找将相应参数应用于sp_query_store_force_plan示例 C 中的存储过程的示例。

sys.query_store_plan 目录视图包括指示计划是否具有关联优化重播脚本的列，并将新状态添加到特定于关联优化重播脚本的现有失败原因列。 在sys.query_store_plan中了解详细信息。

示例

本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。

A. 为数据库启用查询存储和优化计划强制执行

以下代码对数据库启用查询存储，然后对数据库启用优化计划强制执行。 了解有关在 ALTER DATABASE SET 选项中启用查询存储的选项的详细信息。

在运行代码之前，连接到相应的用户数据库。

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. 选择具有优化重播脚本的所有查询

以下示例代码选择查询存储中具有优化重播脚本的所有 query_ids。 在运行示例代码之前，连接到相应的用户数据库。

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

°C 在查询存储中强制执行计划并禁用优化计划强制执行

以下代码在查询存储中强制执行计划，但禁用优化计划强制执行。 在运行以下代码之前，将 @query_id 和 @plan_id 替换为适合你的实例的组合。 sp_query_store_force_plan存储过程要求@replica_group_id在尝试在查询存储中强制禁用优化计划时，将参数作为第三个参数值传入。 可用于禁用特定副本上特定强制计划的优化计划强制。 值 @replica_group_id = 1 用于禁用主要副本上的功能。

EXECUTE sp_query_store_force_plan
    @query_id = 148,
    @plan_id = 4,
    @replica_group_id = 1,
    @disable_optimized_plan_forcing = 1;
GO

在 sp_query_store_force_plan 中了解详细信息。

D. 选择查询存储禁用了优化计划强制执行的所有查询

以下示例查询查询存储is_optimized_plan_forcing_disabled中强制设置到1的所有计划。 在运行代码之前，连接到相应的用户数据库。

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. 对查询禁用优化计划强制执行

以下示例使用 DISABLE_OPTIMIZED_PLAN_FORCING 查询提示查询禁用优化计划强制执行。

SELECT ProductID,
       OrderQty,
       SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

相关内容