查询存储提示最佳做法

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

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

• 有关使用查询存储配置和管理的详细信息，请参阅 使用查询存储监视性能。
• 有关使用查询存储发现可作信息和优化性能的信息，请参阅 使用查询存储优化性能。
• 有关查询存储的一般最佳做法，请参阅 有关使用查询存储监视工作负荷的最佳做法。

查询存储提示的用例

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

无法更改代码时

利用查询存储提示，可以影响查询的执行计划，而无需更改应用程序代码或数据库对象。 没有其他功能支持便捷地应用查询提示。

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

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

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

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

如果由于运行时间要求高或事务负载高，使得代码更改不可能实现，那么查询存储提示可以快速将查询提示应用于现有查询工作负载。 添加和移除查询存储提示非常简单。

可以将查询存储提示添加到批处理查询，以调整异常工作负载突发时段的性能。

作为计划指南的替代方案

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

使用计划指南时，必须使用查询代码片段搜索所有计划。 Query Store 提示功能不需要精确匹配的查询来影响生成的查询执行计划。 查询存储提示可应用于查询存储数据集中的 query_id。

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

考虑较新的兼容性级别

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

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

EXEC 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 查询提示阻止将来执行已知有问题的查询，例如，无状态查询导致资源消耗过高，并影响关键应用程序工作负荷。

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

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

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

请注意以下事项：

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

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

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

  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 提示已被忽略。

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

Related content

• 以 XML 格式保存执行计划
• 显示和保存执行计划
• 在 Azure SQL 数据库中配置最大并行度 (MAXDOP)