通过

查询存储提示最佳做法

适用于： SQL Server 2022 （16.x）及更高版本Azure SQL 数据库Azure SQL 托管实例Microsoft Fabric Preview 中的 SQL 数据库

本文详细介绍了使用查询存储提示的最佳做法。 查询存储提示能够在不修改应用程序代码的情况下调整查询计划。

查询存储提示的用例

将以下用例视作查询存储提示的理想用例。 有关详细信息，请参阅何时使用查询存储提示。

Caution

由于 SQL Server 查询优化器通常会为查询选择最佳执行计划，我们建议资深开发人员和数据库管理员仅在不得已时使用提示。 有关详细信息，请参阅查询提示。

当无法更改代码时

使用查询存储提示可以影响查询的执行计划，而无需更改应用程序代码或数据库对象。 没有其他功能可以让你快速轻松地应用查询提示。

例如，可以使用查询存储提示使提取-转换-加载 (ETL) 工作负荷受益，而无需重新部署代码。 通过这个 14 分钟的视频了解如何使用查询存储提示改进批量加载：

查询存储提示是轻量级的查询优化方法，但如果查询出现问题，应通过更实质性的代码更改来解决。 如果经常需要对查询应用查询存储提示，请考虑对查询进行较大幅度的重写。 SQL Server 查询优化器通常为查询选择最佳执行计划。 建议仅使用提示作为经验丰富的开发人员和数据库管理员的最后手段。

有关可应用哪些查询提示的信息，请参阅支持的查询提示。

在高事务负载或关键任务代码下

如果由于高可用性要求或事务负载而无法更改代码，查询存储提示可以快速对现有查询工作负荷应用查询提示。 添加和删除查询存储提示很简单。

可以将查询存储提示添加到批量查询和从中删除，以调整异常工作负荷突发时段的性能。

作为计划指南的替代方案

在查询存储提示之前，开发人员必须依赖计划指南来完成类似任务，使用起来非常复杂。 查询存储提示与 SQL Server Management Studio (SSMS) 的查询存储功能集成，用于可视化浏览查询。

对于计划指南，需要使用查询片段搜索所有计划。 查询存储提示功能不需要完全匹配的查询即可影响生成的查询计划。 查询存储提示可应用于查询存储数据集中的 query_id。

查询存储提示会替代硬编码的语句级别提示和现有的计划指南。

考虑更新的兼容性级别

例如，当较新的数据库兼容性级别由于供应商规范或测试延迟较长而不可用时，Query Store 提示可能是一个有价值的方法。 如果数据库可使用更高的兼容性级别，请考虑升级单个查询的数据库兼容性级别，以利用 SQL Server 的最新性能优化和功能。

例如，如果 SQL Server 2022 (16.x) 实例的数据库兼容级别为 140，则仍可以使用查询存储提示来运行兼容级别为 160 的单个查询。 可以使用以下提示：

EXECUTE sys.sp_query_store_set_hints
    @query_id = 39,
    @query_hints = N'OPTION(USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160''))';

有关完整教程，请参阅查询存储提示示例。

升级后考虑旧的兼容性级别

另一种情况下查询存储提示可以提供帮助，即在 SQL Server 实例迁移或升级后，查询无法直接修改的情况下。 使用查询存储提示为查询应用先前的兼容性级别，直到可以重写或以其他方式解决，可以在最新的兼容性级别中正常运行。 使用查询存储的回归查询报告、迁移期间使用查询优化助手工具或其他查询级应用程序遥测，标识在较高兼容性级别下出现回归的异常查询。 有关兼容性级别之间差异的详细信息，请查看兼容性级别之间的差异。

在对新的兼容性级别进行性能测试并以这种方式部署查询存储提示后，就可以升级整个数据库的兼容性级别，同时将有问题的关键查询保留在先前的兼容性级别上，而无需更改任何代码。

阻止有问题的查询的未来执行

可以使用 ABORT_QUERY_EXECUTION 查询提示阻止将来执行已知有问题的查询，例如，无状态查询导致资源消耗过高，并影响关键应用程序工作负荷。

Note

ABORT_QUERY_EXECUTION查询提示仅适用于 Azure SQL 数据库、Azure SQL 托管实例AUTD 和 SQL Server 2025 （17.x） 预览版。

例如，若要阻止将来执行 query_id 39，请执行 sys.sp_query_store_set_hints ，如下所示：

EXECUTE sys.sp_query_store_set_hints
    @query_id = 39,
    @query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';

若要解除对同一查询的阻止，请执行 sys.sp_query_store_clear_hints：

EXECUTE sys.sp_query_store_clear_hints @query_id = 39;

有关详细信息，请参阅查询存储提示 示例。

请注意以下事项：

  • 为查询指定此提示时，尝试执行查询失败，错误为 8778，严重性为 16，查询 执行已中止，因为指定了ABORT_QUERY_EXECUTION提示。

  • 若要取消阻止查询，可以通过将 query_id 值传递到 @query_id 参数来清除 sys.sp_query_store_clear_hints 存储过程中的提示。

    • 此存储过程清除查询的所有提示。 如果要在取消阻止查询时保留现有提示，请使用 sys.sp_query_store_set_hints，删除 ABORT_QUERY_EXECUTION 提示但保留其他提示。

  • 可以使用系统视图查找被阻止的查询存储中的查询，如以下示例查询中所示：

    SELECT qsh.query_id,
       q.query_hash,
       qt.query_sql_text
FROM sys.query_store_query_hints AS qsh
     INNER JOIN sys.query_store_query AS q
         ON qsh.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS qt
         ON q.query_text_id = qt.query_text_id
WHERE UPPER(qsh.query_hint_text) LIKE '%ABORT[_]QUERY[_]EXECUTION%';

  • 若要获得 query_id 的值，查询存储中必须记录至少一次查询执行。 此执行不必成功。 这意味着可以阻止超时或取消的查询的未来执行。

  • 如果需要阻止或取消阻止具有特定查询哈希的所有查询，请考虑使用自动化脚本。 例如，dbo.sp_query_store_modify_hints_by_query_hash是一个示例存储过程，它在循环中为所有与查询哈希匹配的sys.sp_query_store_set_hints值调用sys.sp_query_store_clear_hints或query_id系统存储过程。

  • 如果查询在您阻止它时已开始执行，则执行将继续。 可以使用 KILL 语句中止查询。

    • 已终止的查询的执行不会记录在查询存储中。 如果查询尚未在查询存储中，需要让查询完成或超时以获取可阻止的 query_id。

  • 当一个查询被ABORT_QUERY_EXECUTION提示阻止时，execution_type视图中的execution_type_desc和列分别被设置为4和Exception。

  • 与其他所有查询存储提示一样，您需要拥有数据库上的 ALTER 权限才能设置和清除 ABORT_QUERY_EXECUTION 提示。

查询存储提示注意事项

部署查询存储提示时，请考虑以下场景。

数据分布变化

计划指南、通过查询存储的强制计划，以及查询存储提示可替代优化器做决策。 查询存储提示现在可能有益，但将来可能并非如此。 例如，如果查询存储提示在以前的数据分布中对查询有所帮助，那么如果大规模DML操作改变了数据，它可能会适得其反。 新的数据分布可能会导致优化器做出比提示更好的决策。 这种情况是强制计划行为最常见的结果。

定期重新评估查询存储提示策略

在以下情况下重新评估现有的查询存储提示策略：

  • 已知大型数据分发更改后。
  • 数据库可用的资源发生更改时。 例如，当 Azure SQL 数据库、SQL 托管实例或 SQL Server 虚拟机的计算大小发生更改时。
  • 计划修复已经持续了很长时间的情况下。 查询存储提示最适合用于短期修复。
  • 意外的性能回归。

广泛的潜在影响

无论参数集、源应用程序、用户或结果集如何，查询存储提示都会影响查询的所有执行。 对于意外的性能回归，可以使用 sys.sp_query_store_clear_hints 轻松移除使用 sys.sp_query_store_set_hints 创建的查询存储提示。

在生产环境中应用查询存储提示之前，仔细地对任务关键型系统或敏感系统的更改进行负载测试。

强制参数化和 RECOMPILE 提示不受支持

当数据库选项 RECOMPILE 时，不支持通过查询存储提示应用 查询提示。 有关详细信息，请参阅使用强制参数化的指南。

该 RECOMPILE 提示与数据库级别设置的强制参数化不兼容。 如果数据库使用强制参数化，并且 RECOMPILE 提示是查询的查询存储中设置的提示字符串的一部分，则数据库引擎将忽略 RECOMPILE 提示，并在指定时应用其他提示。 此外，从 2022 年 7 月在 Azure SQL 数据库中开始，发出警告（错误代码 12461），指出 RECOMPILE 提示已被忽略。

有关可应用哪些查询提示的信息，请参阅支持的查询提示。

相关内容