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

项目
08/30/2023

适用于： SQL Server 2022 (16.x)

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

优化计划强制执行的实现

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

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

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

注意事项

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

只有经过完全优化的查询计划才符合条件，这可通过查看是否存在 StatementOptmLevel="FULL" 属性进行验证。
具有 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)。

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

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

示例

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

以下代码对数据库启用查询存储，然后对数据库启用优化计划强制执行。有关启用查询存储的选项的详细信息，请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。

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

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 参数作为第三个参数值传入。可用于禁用特定副本上特定强制计划的优化计划强制。值为 1 - @replica_group_id=1 将用于禁用主要副本上的功能。

EXEC 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 (Transact-SQL)。

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查询提示查询禁用优化计划强制执行。该示例使用 AdventureWorks 示例数据库。

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

后续步骤

通过以下文章详细了解查询存储和优化计划强制执行：