查询存储提示(预览版)

适用于:yesSQL Server(所有受支持的版本)YesAzure SQL 数据库YesAzure SQL 托管实例

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

查询存储提示是 SQL Server 2022 (16.x) 预览版中的预览功能。 Azure SQL 数据库和 Azure SQL 托管实例中提供查询存储提示功能。

注意

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

概述

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

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

何时使用查询存储提示

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

The workflow for Query Store Hints.

  First the query is executed, then captured by Query Store. Then the DBA creates a Query Store hint on a query. Thereafter, the query is executed using the Query Store hint.

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

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

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

  1. 确定你希望修改的查询语句的查询存储 query_id。 可通过多种方式执行此操作:1.1. 查询查询存储目录视图。 1.2. 使用 SQL Server Management Studio 内置查询存储报表。 1.3. 使用适用于 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 特性

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

Attribute 说明
QueryStoreStatementHintText 应用于查询的实际查询存储提示
QueryStoreStatementHintId 查询提示的唯一标识符
QueryStoreStatementHintSource 查询存储提示的源(例如“用户”)

注意

在查询存储提示的预览期间,这些 XML 元素将仅通过 Transact-SQL 命令 SET STATISTICS XMLSET SHOWPLAN XML 的输出提供。

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

  • 查询存储提示会替代其他硬编码语句级别提示和计划指南。
  • 查询将始终在任何对立的查询存储提示被忽略(否则会导致错误)的位置执行。
  • 如果查询存储提示相矛盾,SQL Server 不会阻止查询执行,也不会应用查询存储提示。
  • 简单参数化 - 符合简单参数化条件的语句不支持查询存储提示。
  • 强制参数化 - RECOMPILE 提示与数据库级别的强制参数化集不兼容。 如果数据库具有强制参数化集,并且 RECOMPILE 提示是查询存储中提示字符串集的一部分,则 SQL Server 将忽略 RECOMPILE 提示,并将应用任何其他提示(如果已使用)。
    • 此外,SQL Server 将发出警告(错误代码 12460),指出 RECOMPILE 提示已被忽略。
    • 有关强制参数化用例注意事项的详细信息,请参阅强制参数化使用指南
  • 目前,查询存储提示可以应用于 Always On 可用性组的主要副本。

查询存储提示最佳做法

  • 在评估针对潜在新查询存储提示的查询之前,请完成索引和统计信息维护。
  • 在利用查询存储提示之前,请在最新的兼容性级别上测试应用数据库。 * 例如,在 SQL Server 2022(兼容级别 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_textsys.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_hintsquery_id 39 中删除提示。

EXEC sys.sp_query_store_clear_hints @query_id = 39;

另请参阅

后续步骤