查询存储提示

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

本文概述了如何使用查询存储应用查询提示。 查询存储提示提供了一种易于使用的方法，可在不更改应用程序代码的情况下制定查询计划。

Azure SQL 数据库和 Azure SQL 托管实例中提供查询存储提示功能。 查询存储提示也是 SQL Server 2022 (16.x) 中引入 SQL Server 的一项功能。

• 若要详细了解如何使用查询存储进行配置和管理，请参阅使用查询存储监视性能。
• 如需详细了解如何使用查询存储发现可操作信息并优化性能，请参阅使用查询存储优化性能。
• 有关在 Azure SQL 数据库中运行查询存储的信息，请参阅 在 Azure SQL 数据库中运行查询存储。

注意

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

观看此视频，大致了解查询存储提示：

概述

理想情况下，查询优化器为查询选择最佳执行计划。

如果没有选择最佳计划，开发人员或 DBA 可能希望针对特定条件进行手动优化。 查询提示通过 OPTION 子句指定，可用于影响查询执行行为。 虽然查询提示有助于为各种性能相关问题提供本地化解决方案，但它们要求重写原始查询文本。 数据库管理员和开发人员可能并不总是能够直接更改 Transact-SQL 代码来注入查询提示。 Transact-SQL 可硬编码到应用程序中，也可由应用程序自动生成。 以前，开发人员可能必须依赖计划指南，这可能用起来很复杂。

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

何时使用查询存储提示

如名称所示，此功能扩展并依赖于查询存储。 查询存储可捕获查询、执行计划和关联的运行时统计信息。 查询存储可极大地简化整体性能优化客户体验。 SQL Server 2016 (13.x) 首先引入了查询存储，现在默认在 SQL Server 2022 (16.x)、Azure SQL 托管实例 和 Azure SQL 数据库中启用。

首先执行查询，再由查询存储捕获。 然后，DBA 对查询创建一个查询存储提示。 接着，使用查询存储提示执行查询。

有关查询存储提示可在哪些方面帮助解决查询级别的性能问题的示例：

• 在每次执行时重新编译查询。
• 限制批量插入操作的内存授予上限。
• 限制更新统计信息时的最大并行度。
• 使用哈希联接而不是嵌套循环联接。
• 对特定查询使 兼容性级别 110，同时将数据库中其他所有内容都保留为兼容级别 150。
• 禁用 SELECT TOP 查询的行目标优化。

若要使用查询存储提示，请执行以下操作：

1. 确定你希望修改的查询语句的查询存储 query_id。 可通过多种方式执行此操作：
   • 查询查询存储目录视图。
   • 使用 SQL Server Management Studio 内置查询存储报表。
   • 使用适用于 Azure SQL 数据库的 Azure 门户 Query Performance Insight。
2. 使用你想要应用于查询的 query_id 和查询提示字符串执行 sys.sp_query_store_set_hints。 此字符串可包含一个或多个查询提示。 有关完整信息，请参阅 sys.sp_query_store_set_hints。

创建后，查询存储提示将持久保存，在重启和故障转移后仍然存在。 查询存储提示会替代硬编码的语句级别提示和现有的计划指南提示。

如果查询提示与查询优化可能的结果相冲突，则其将不会阻止查询执行并且不会应用提示。 如果提示导致查询失败，则会忽略提示，并可在 sys.query_store_query_hints 中查看最新的失败详细信息。

查询存储提示系统存储过程

若要创建或更新提示，请使用 sys.sp_query_store_set_hints。 在有效字符串格式 N'OPTION (...)' 中指定提示。

• 创建查询存储提示时，如果特定 query_id 没有查询存储提示，会创建新的查询存储提示。
• 创建或更新查询存储提示时，如果特定 query_id 已存在查询存储提示，则提供的最后一个值将替代之前为关联查询指定的值。
• 如果 query_id 不存在，将引发错误。

注意

有关支持的提示的完整列表，请参阅 sys.sp_query_store_set_hints。

若要删除与 query_id 关联的提示，请使用 sys.sp_query_store_clear_hints。

执行计划 XML 特性

应用提示后，以下结果集将以 XML 格式显示在执行计划的 StmtSimple 元素中：

属性	描述
QueryStoreStatementHintText	应用于查询的实际查询存储提示
QueryStoreStatementHintId	查询提示的唯一标识符
QueryStoreStatementHintSource	查询存储提示的源（例如“用户”）

注意

这些 XML 元素通过 Transact-SQL 命令 SET STATISTICS XML 和 SET SHOWPLAN XML 的输出提供。

查询存储提示和功能互操作性

• 查询存储提示会替代其他硬编码语句级别提示和计划指南。
• 查询始终执行。 对立的查询存储提示会被忽略，否则会导致错误。
• 如果查询存储提示相矛盾，SQL Server 不会阻止查询执行，也不会应用查询存储提示。
• 简单参数化 - 符合简单参数化条件的语句不支持查询存储提示。
• 强制参数化 - RECOMPILE 提示与数据库级别的强制参数化集不兼容。 如果数据库具有强制参数化集，并且 RECOMPILE 提示是查询存储中提示字符串集的一部分，则 SQL Server 会忽略 RECOMPILE 提示，并将应用任何其他提示（如果已使用）。
  • 此外，SQL Server 会发出警告（错误代码 12461），指出 RECOMPILE 提示已被忽略。
  • 有关强制参数化用例注意事项的详细信息，请参阅强制参数化使用指南。
• 手动创建的查询存储提示无需清理。 自动保留捕获策略不会从查询存储中清理提示和查询。
  • 查询可以被用户手动移除，这也将移除关联的查询存储提示。
  • CE 反馈自动生成的查询存储提示会被自动保留的捕获策略清理。
  • DOP 反馈和内存授予反馈可在不使用查询存储提示的情况下塑造查询行为。 通过自动保留捕获策略清理查询时，DOP 反馈和内存授予反馈数据也会被清理。
  • 可以手动创建 CE 反馈实现的同一查询存储提示，然后带有该提示的查询将不再会被捕获策略自动保留清理。

查询存储提示和可用性组

除非启用了辅助副本查询存储，否则查询存储提示对次要副本没有影响。 有关详细信息，请参阅次要副本的查询存储。

• 在 SQL Server 2022 (16.x) 之前，可以对可用性组的主要副本应用查询存储提示。
• 从 SQL Server 2022 (16.x) 开始，当启用次要副本的查询存储时，查询存储提示对于可用性组中的次要副本也是副本感知的。
• 启用次要副本的查询存储时，可以将查询存储提示添加到特定副本或副本集。 在 sys.sp_query_store_set_query_hints 中，这是由 SQL Server 2022 (16.x) 中引入的 @query_hint_scope 参数设置的。
• 通过查询 sys.query_store_replicas 查找可用的副本集。
• 使用 sys.query_store_plan_forcing_locations 查找在次要副本上强制执行的计划。

查询存储提示最佳做法

• 在评估针对潜在新查询存储提示的查询之前，请完成索引和统计信息维护。
• 在使用查询存储提示之前，请在最新的兼容性级别上测试应用数据库。
  • 例如，在 SQL Server 2022 (16.x)（兼容级别 160）中引入了参数敏感计划 (PSP) 优化，该优化使用每个查询的多个活动计划来解决不均匀的数据分布。 如果环境无法使用最新的兼容性级别，则使用 RECOMPILE 提示的查询存储提示可以在任何支持的兼容性级别上使用。
• 查询存储提示会替代 SQL Server 查询计划行为。 建议仅在需要解决与性能相关的问题时才使用查询存储提示。
• 建议在数据分布发生变化的任何时间和数据库迁移项目期间重新评估查询存储提示、语句级提示、计划指南和查询存储强制计划。 数据分布的变化可能会导致查询存储提示生成欠佳的执行计划。

示例

A. 查询存储提示演示

下面演练了 Azure SQL 数据库中的查询存储提示，它通过 BACPAC 文件 (.bacpac) 使用导入的数据库。 若要了解如何将新的数据库导入到 Azure SQL 数据库服务器，请参阅快速入门：将 BACPAC 文件导入数据库。

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. 在查询存储中标识查询

以下示例查询 sys.query_store_query_text 和 sys.query_store_query，以返回执行的查询文本片段的 query_id。

在此演示中，我们尝试优化的查询位于 SalesLT 示例数据库中：

SELECT * FROM SalesLT.Address as A 
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

查询存储不会立即将查询数据反映到其系统视图中。

确定查询存储系统目录视图中的查询：

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
    qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%PostalCode =%'  
  AND query_sql_text not like N'%query_store%';
GO

在以下示例中，SalesLT 数据库中的上一个查询示例被标识为 query_id 39。

标识后，应用提示以对 query_id 强制实施最大内存授予大小（以配置的内存限制百分比表示）：

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

还可使用以下语法应用查询提示，例如强制使用旧版基数估计器的选项：

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

可使用逗号分隔列表应用多个查询提示：

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

在查询存储提示中就地查看 query_id 39：

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc 
FROM sys.query_store_query_hints 
WHERE query_id = 39;

最后，使用 sp_query_store_clear_hints 从 query_id 39 中删除提示。

EXEC sys.sp_query_store_clear_hints @query_id = 39;

相关内容

• sys.query_store_query_hints (Transact-SQL)
• sys.sp_query_store_set_hints (Transact-SQL)
• sys.sp_query_store_clear_hints (Transact-SQL)
• 以 XML 格式保存执行计划
• 显示和保存执行计划
• 提示 (Transact-SQL) - 查询
• 使用查询存储监视工作负载的最佳做法
• 查询存储提示最佳做法
• 使用查询存储来监视性能
• 在 Azure SQL 数据库中配置最大并行度 (MAXDOP)