查询存储提示

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

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

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

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

Overview

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

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

查询存储提示“Query Store hints”允许您在不直接修改查询 Transact-SQL 文本的情况下，将查询提示注入到查询中，从而解决这一问题。 有关可应用哪些查询提示的信息，请参阅支持的查询提示。

何时使用查询存储提示

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

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

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

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

1. 确定你希望修改的查询语句的查询存储 query_id。 您可以通过多种方式来做这件事：

   • 查询查询存储的目录视图（Transact-SQL）。
   • 使用 SQL Server Management Studio 内置查询存储报表。
   • 使用适用于 Azure SQL 数据库的 Azure 门户 Query Performance Insight。

2. 使用你想要应用于查询的 sys.sp_query_store_set_hints 和查询提示字符串执行 query_id。 此字符串可包含一个或多个查询提示。 有关完整信息，请参阅 sys.sp_query_store_set_hints。

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

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

在您使用查询存储提示之前

在开始使用查询存储提示之前，请考虑以下事项。

• 在评估可能的新查询存储提示的查询之前，请完成统计信息维护和索引维护（如有必要）。 统计信息维护，以及在较次要程度上的索引维护，可以解决本来需要查询提示的问题。
• 在使用查询存储提示之前，请在最新的 兼容级别 测试应用程序数据库，以确定这是否解决了需要查询提示的问题。
  • 例如，在兼容级别 160 下的 SQL Server 2022（16.x）中引入了参数敏感计划（PSP）优化。 它为每个查询使用多个活跃计划来解决不均匀数据分布的问题。 如果环境无法使用最新的兼容级别，可以在任何支持兼容级别的情况下使用RECOMPILE提示的查询存储提示。
• 查询存储提示会替代数据库引擎默认查询计划行为。 仅当需要解决性能相关问题时，才应使用查询存储提示。
• 应在数据量和分布发生变化以及进行数据库迁移项目时，重新评估查询存储提示、语句级提示、计划指南，以及查询存储中强制执行的计划。 数据量和分发中的更改可能会导致查询存储提示生成不理想的执行计划。

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

若要创建或更新提示，请使用 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 特性

应用提示时，以下结果集以 StmtSimpleXML 格式显示在执行计划的元素中：

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

• 查询存储提示会替代其他硬编码语句级别提示和计划指南。
• 除了使用 ABORT_QUERY_EXECUTION 提示的情况，使用查询存储提示的查询总是被执行。 对立的查询存储提示会被忽略，否则会导致错误。
• 如果查询存储提示相矛盾，则数据库引擎不会阻止查询执行，并且未应用查询存储提示。
• 对于符合简单参数化条件的语句，不支持查询存储区提示。
• 该 RECOMPILE 提示与数据库级别设置的强制参数化不兼容。 如果数据库已设置强制参数化，并且 RECOMPILE 提示是查询存储提示的一部分，则数据库引擎将忽略 RECOMPILE 提示，并应用指定的任何其他提示。
  • 数据库引擎发出警告（错误代码 12461），指出 RECOMPILE 提示已被忽略。
  • 有关强制参数化用例注意事项的详细信息，请参阅 使用强制参数化指南。
• 手动创建的查询存储提示不受查询存储清理的豁免。 自动保留捕获策略不会清理提示和查询。
  • 用户可以手动删除查询。 这还会删除关联的查询存储提示。
  • CE 反馈自动生成的查询存储提示会被自动保留的捕获策略清理。
  • DOP 反馈和内存授予反馈可在不使用查询存储提示的情况下塑造查询行为。 当查询被自动保留捕获策略清理时，DOP 反馈和内存分配反馈数据也会被清理。
  • 如果您创建了与 CE 反馈手动实现相同的查询存储提示，则带有该提示的查询将不再受自动保留策略的影响进行清除。

查询存储提示和次要副本

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

• 在 SQL Server 2022（16.x）和早期版本中，查询存储提示只能应用于主副本。
• 在 SQL Server 2022（16.x）及更高版本中，启用辅助副本的查询存储时，可以在可用性组中的辅助副本上应用查询存储提示。
• 当启用了用于辅助副本的查询存储时，您可以添加查询存储提示，使其仅在特定的副本组上生效。 为此，在调用@replica_group_id时使用参数。 相反，您可以使用sys.sp_query_store_clear_query_hints从特定副本组中删除 Query Store 提示。
• 通过查询 sys.query_store_replicas查找可用的副本组。
• 使用 sys.query_store_plan_forcing_locations 查找在次要副本上强制执行的计划。

Examples

A. 查询存储提示演示

以下是通过 BACPAC 文件（.bacpac）导入的数据库来演示的 Azure SQL 数据库中查询存储提示的操作指南。 了解如何将新数据库导入 Azure SQL 数据库服务器，请参阅 快速入门：将 bacpac 文件导入 Azure SQL 数据库或 Azure SQL 托管实例中的数据库。

-- ************************************************************************ --
-- 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;

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

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Related content

• 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)