管理查询存储的最佳做法

项目
01/08/2024

适用于： SQL Server Azure SQL 数据库 Azure SQL 托管实例

本文概述了 SQL Server 查询存储的管理及其相关功能。

若要详细了解如何使用查询存储进行配置和管理，请参阅使用查询存储监视性能。

注意

在 SQL Server 2022 (16.x) 中，所有新创建的 SQL Server 数据库默认启用查询存储，以便更好地跟踪性能历史记录、排查查询计划相关问题并启用新的查询处理器功能。

Azure SQL 数据库中的查询存储默认值

本部分介绍 Azure SQL 数据库中的最佳配置默认值，这些默认值旨在确保查询存储以及依赖功能能够可靠运行。默认配置已针对持续数据收集操作进行优化，即，在 OFF/READ_ONLY 状态下花费最少的时间。有关所有可用的查询存储选项的详细信息，请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。

配置	说明	默认	评论
MAX_STORAGE_SIZE_MB	指定 Query Store 在客户数据库中占用的数据空间的限制	100，SQL Server 2019 (15.x) 之前的版本 1000，从 SQL Server 2019 (15.x) 开始	对新数据库强制实施
INTERVAL_LENGTH_MINUTES	定义聚合和持久化查询计划收集运行时统计信息的时段大小。每个活动查询计划将为此配置定义的时间段包含最多一行	60	对新数据库强制实施
STALE_QUERY_THRESHOLD_DAYS	基于时间的清理策略，控制持久化运行时统计信息和非活动查询的保留期	30	对新数据库和使用以前的默认值 (367) 的数据库强制实施
SIZE_BASED_CLEANUP_MODE	指定当 Query Store 数据大小接近限制时是否自动清理数据	AUTO	对所有数据库强制实施
QUERY_CAPTURE_MODE	指定是要跟踪所有查询，还是只跟踪一部分查询	AUTO	对所有数据库强制实施
DATA_FLUSH_INTERVAL_SECONDS	指定捕获的运行时统计信息在刷新到磁盘之前，保留在内存中的最大期限	900	对新数据库强制实施

重要

在查询存储的最终激活阶段，系统会在 Azure SQL 数据库中自动应用这些默认值。启用后，Azure SQL 数据库不会更改客户设置的配置值，除非这些值对主要工作负载或查询存储的可靠运行造成负面影响。

注意

无法在 Azure SQL 数据库的单一数据库和弹性池中禁用查询存储。执行 ALTER DATABASE [database] SET QUERY_STORE = OFF 将返回警告“'QUERY_STORE=OFF' is not supported in this version of SQL Server.”

如果想要保持使用自定义设置，请结合 Query Store 选项使用 ALTER DATABASE，将配置还原到以前的状态。请查看查询存储最佳做法，了解如何选择最佳的配置参数。

设置最佳查询存储捕获模式

在 Query Store 中保留最相关数据。下表描述了每个查询存储捕获模式的典型方案：

Query Store 捕获模式	场景
全部	对工作负载进行彻底地分析，分析所有查询的形状及其执行频率和其他统计信息。识别工作负荷中的新查询。检测是否使用即席查询来识别用户或自动参数化的机会。注意：这是 SQL Server 2016 (13.x) 和 SQL Server 2017 (14.x) 中的默认捕获模式。
Auto	关注相关且可操作的查询。例如，那些定期执行的查询或资源消耗很大的查询。注意：在 SQL Server 2019 (15.x) 及更高版本中，这是默认捕获模式。
无	你已经捕获了需要在运行时监视的查询集，因此需消除其他查询可能会带来的干扰。 “无”适用于测试和基准测试环境。 “无”也适用于需要提供已配置的 Query Store 配置来监视其应用程序工作负荷的软件供应商。在使用“无”时应格外小心，因为可能无法跟踪和优化重要的新查询。避免使用“无”，除非你的特定方案需要使用它。
自定义	SQL Server 2019 (15.x) 在 `ALTER DATABASE ... SET QUERY_STORE` 命令下引入了自定义捕获模式。虽然“Auto”是默认设置且建议使用，但如果担心查询存储可能会引入开销，数据库管理员可以使用自定义捕获策略进一步优化查询存储捕获行为。有关详细信息和建议，请参阅本文后面的自定义捕获策略。有关此语法的详细信息，请参阅 ALTER DATABASE SET 选项。

注意

当查询存储捕获模式设置为“全部”、“自动”或“自定义”时，始终捕获游标、存储过程中的查询和本机编译的查询。若要捕获本机编译的查询，请使用 sys.sp_xtp_control_query_exec_stats 启用每个查询统计信息的收集。

在 Query Store 中保留最相关数据

将查询存储配置为只包含最相关的数据，这样在持续运行的时候对常规工作负载的影响最小，方便进行故障排除。

下表提供最佳实践：

最佳做法	设置
对保留的历史数据进行限制。	配置基于时间的策略以激活自动清理功能。
筛选掉不相关的查询。	将“查询存储捕获模式”配置为“自动”。
达到最大大小时，删除不太相关的查询。	激活基于大小的清理策略。

自定义捕获策略

启用 CUSTOM 查询存储捕获模式后，可以在新的“查询存储捕获策略设置”下使用其他查询存储配置，以微调特定服务器中的数据收集。

新的自定义设置定义在内部捕获策略时间阈值期间执行的操作。这是评估配置条件的时间边界，如果所有值为 true，则查询存储可以捕获查询。

查询存储捕获模式指定了查询存储的查询捕获策略。

All：捕获所有查询。此选项是 SQL Server 2016 (13.x) 和 SQL Server 2017 (14.x) 中的默认选项。
Auto：忽略不太频繁的查询以及编译和执行持续时间不长的查询。执行计数、编译和运行时持续时间的阈值由内部决定。从 SQL Server 2019 (15.x) 开始，这是默认选项。
None：查询存储停止捕获新查询。
Custom：支持额外控件和微调数据收集策略功能。新的自定义设置定义在内部捕获策略时间阈值期间执行的操作。这是评估配置条件的时间边界，如果所有值为 true，则查询存储可以捕获查询。

在下列情况下，应考虑为环境优化适当的自定义捕获策略：

数据库非常大。
数据库有大量唯一的临时查询。
数据库有特定大小或增长限制。

SSMS
T-SQL

若要查看 Management Studio 中的当前设置：

在 SQL Server Management Studio 对象资源管理器中，右键单击数据库。
选择“属性”。
选择查询存储。在查询存储页面上，验证操作模式（请求）是否为 Read write。
将查询存储捕获模式更改为 Custom。
注意，查询存储捕获策略下的四个捕获策略字段现已启用并可配置。

自定义捕获策略示例

以下示例将 QUERY_CAPTURE_MODE 设置为 AUTO 并设置自定义捕获模式。以下每一项都将自定义捕获策略设置为 SQL Server 2022 (16.x) 中的默认值。考虑调整这些值，以减少捕获的查询数，从而减少查询存储的磁盘占用空间。建议按小增量逐步更改这些值。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

以下示例查询将更改现有查询存储，以使用自定义捕获策略来替代 EXECUTION_COUNT 和 TOTAL_COMPILE_CPU_TIME_MS 的默认设置。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

查询存储最大大小

从 SQL Server 2019 (15.x) 开始，查询存储的默认最大大小值为 1000 MB。在以前的版本中，默认值为 100 MB。在具有多个唯一查询计划的忙碌数据库中，增加查询存储的最大大小限制较为合适。调整捕获策略（见上一节）是限制查询存储的磁盘大小并防止查询存储进入 READ_ONLY 模式的重要考虑因素。当查询存储收集查询、执行计划和统计信息时，其在数据库中的大小会一直增长，直至达到此限制。达到此限制后，Query Store 会自动将操作模式更改为 READ_ONLY，并停止收集新数据，这意味着你的性能分析自此不再精确。

在 SQL Server 和 Azure SQL 托管实例中，不会严格执行限制 MAX_STORAGE_SIZE_MB。
在 Azure SQL 数据库中，允许的最大 MAX_STORAGE_SIZE_MB 值为 10,240 MB。

仅当查询存储将数据写入磁盘时才检查存储大小。此间隔由 DATA_FLUSH_INTERVAL_SECONDS 选项或 Management Studio 查询存储对话框选项“数据刷新间隔”设置。

间隔时间默认值为 900 秒（或 15 分钟）。
如果查询存储已违反存储大小检查之间的 MAX_STORAGE_SIZE_MB 限制，则转换为只读模式。
如果启用了 SIZE_BASED_CLEANUP_MODE，则也会触发强制实施 MAX_STORAGE_SIZE_MB 限制的清理机制。
- 清除足够的空间后，查询存储模式将自动切换回 READ_WRITE 模式。

有关详细信息，请参阅 ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MB。

数据刷新间隔（分钟）

数据刷新间隔定义了收集的运行时统计信息保存到磁盘之前的频率。在 SQL Server Management Studio 中，该值以分钟为单位，但在 Transact-SQL 中，该值以秒为单位表示。默认值为 15 分钟（900 秒）。

增加数据刷新间隔会降低查询存储存储 I/O 的总体影响，但会导致存储 I/O 工作负载达到高峰，对磁盘利用率的影响较小但更严重。如果工作负载不生成大量不同的查询和计划或者你能够接受在数据库关闭之前花更长的时间来保留数据，可考虑使用更大的值。
减少数据刷新间隔会减少在关闭、断电或故障转移时丢失的查询存储数据量。它还可以通过更频繁地写入磁盘，但使用更少的数据来平滑查询存储的 I/O 影响。

注意

如果出现故障转移或关闭命令，使用跟踪标志 7745 会阻止查询存储数据写入磁盘。有关详细信息，请参阅在任务关键型服务器中使用查询存储。

修改查询存储默认值

根据工作负荷和性能故障排除要求来配置 Query Store。默认参数是启动的理想参数，但应监视查询存储在一定时段内的行为表现，并对其配置进行相应的调整。

查看查询存储当前设置

查看 SQL Server Management Studio (SSMS) 或 T-SQL 中的当前查询存储设置。

SSMS
T-SQL

若要查看 Management Studio 中的当前设置：

在 SQL Server Management Studio 对象资源管理器中，右键单击数据库。
选择“属性”。
选择查询存储。

执行以下 T-SQL 脚本，以便获取有关查询存储大小的最新信息：

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
 max_storage_size_mb, readonly_reason
FROM sys.database_query_store_options;

以下脚本将设置新的“最大大小 (MB)”值：

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

使用 SQL Server Management Studio 或 Transact-SQL 为“数据刷新间隔”设置不同的值：

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

统计信息收集间隔：定义收集的运行时统计信息的粒度级别（以分钟为单位）。默认值为 60 分钟。如果需要更细的粒度或更短的时间来检测和缓解问题，可考虑使用较小的值。请记住，该值会直接影响查询存储数据的大小。使用 SQL Server Management Studio 或 Transact-SQL 为“统计信息收集间隔”设置不同的值：

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

过时查询阈值（天）：基于时间的清除策略，用于控制持久化运行时统计信息和非活动查询的保持期（以天为单位）。查询存储默认配置为将数据保留 30 天，这对于你的方案来说可能过长。

避免保留你并不打算使用的历史数据。这样可以减少变为只读状态的次数。查询存储数据的大小以及检测和解决问题的时间将会变得更可预测。使用 Management Studio 或以下脚本配置基于时间的清理策略：

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

基于大小的清理模式：指定在查询存储数据大小达到限制时，是否启用自动数据清理功能。请激活基于大小的清理功能，确保查询存储始终以读写模式运行并收集最新数据。在繁重的工作负载下，不能保证查询存储清理会始终将数据大小保持在限制范围内。自动数据清除可能会落后并切换（暂时）到只读模式。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Query Store 捕获模式：指定查询存储的查询捕获策略。

All：捕获所有查询。此选项是 SQL Server 2016 (13.x) 和 SQL Server 2017 (14.x) 中的默认选项。
Auto：忽略不太频繁的查询以及编译和执行持续时间不长的查询。执行计数、编译和运行时持续时间的阈值由内部决定。从 SQL Server 2019 (15.x) 开始，这是默认选项。
None：查询存储停止捕获新查询。
Custom：支持额外控件和微调数据收集策略功能。新的自定义设置定义在内部捕获策略时间阈值期间执行的操作。这是评估配置条件的时间边界，如果所有值为 true，则查询存储可以捕获查询。

重要

以下脚本将 QUERY_CAPTURE_MODE 设置为 AUTO：

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

示例

以下示例将 QUERY_CAPTURE_MODE 设置为 AUTO，并在 SQL Server 2016 (13.x) 中设置其他建议选项：

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

以下示例将 QUERY_CAPTURE_MODE 设置为 AUTO，并在 SQL Server 2017 (14.x) 中设置其他建议选项以包括等待统计信息：

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

以下示例将 CUSTOM 捕获策略设置为 SQL Server 2019 (15.x) 默认值，而不是新的默认 AUTO 捕获模式。有关自定义捕获策略选项和默认值的详细信息，请参阅 <query_capture_policy_option_list>。

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

查询存储维护

本部分提供一些有关如何管理查询存储功能本身的准则。

查询存储状态

查询存储将其数据存储在用户数据库内，正因为此，它具有大小限制（使用 MAX_STORAGE_SIZE_MB 进行配置）。如果查询存储中的数据命中该限制，则查询存储将自动从读写状态更改为只读状态，并停止收集新数据。

查询 sys.database_query_store_options ，以确定当前查询存储是否可用，以及当前是否在收集运行时状态。

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

查询存储状态是由 actual_state 列决定的。如果不处于所需状态，请查看 readonly_reason 列，了解详细信息。当查询存储大小超过配额时，该功能将切换到 read_only 模式，并提供原因。有关原因的信息，请参阅 sys.database_query_store_options (Transact-SQL)。

获取查询存储选项

若要了解查询存储状态的相关详细信息，请在用户数据库中执行以下操作。

SELECT * FROM sys.database_query_store_options;

设置查询存储间隔

你可以覆盖用于聚合查询运行时统计信息的时间间隔（默认值为 60 分钟）。通过 sys.database_query_store_options 视图公开时间间隔的新值。

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

INTERVAL_LENGTH_MINUTES 不允许使用任意值。可以使用下列间隔之一：1、5、10、15、30、60 或 1440 分钟。

注意

对于 Azure Synapse Analytics，不支持自定义查询存储配置选项，如本部分所示。

查询存储空间使用情况

若要检查当前的查询存储大小和限制，请在用户数据库中执行以下语句。

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

如果查询存储已满，请使用以下语句来扩展存储。

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

获取查询存储选项

你可以使用单个 ALTER DATABASE 语句同时设置多个查询存储选项。

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

有关配置选项的完整列表，请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。

清理空间

查询存储时间间隔表是在数据库创建期间在 PRIMARY 文件组中创建的，且之后不可更改此配置。如果空间已用完，可能需要使用以下语句来清除更旧的查询存储数据。

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

或者，你可以只清理临时查询数据，因为此数据与查询优化和计划分析的相关性更低，但却占用了大量空间。

在 Azure Synapse Analytics 中，清除查询存储不可用。系统会自动保留过去 7 天的数据。

删除临时查询

这将从查询存储中清除即席查询和内部查询，以便查询存储不会耗尽空间并删除我们真正需要跟踪的查询。

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

你可以使用其他逻辑来定义自己的过程，以清理不再需要的数据。

以上示例使用 sp_query_store_remove_query 扩展存储过程来删除不必要的数据。也可执行以下操作：

使用 sp_query_store_reset_exec_stats 清除给定计划的运行时统计信息。
使用 sp_query_store_remove_plan 删除单个计划。

通过

管理查询存储的最佳做法

Azure SQL 数据库中的查询存储默认值

设置最佳查询存储捕获模式

在 Query Store 中保留最相关数据

自定义捕获策略

自定义捕获策略示例

查询存储最大大小

数据刷新间隔（分钟）

修改查询存储默认值

查看查询存储当前设置

示例

查询存储维护

查询存储状态

获取查询存储选项

设置查询存储间隔

查询存储空间使用情况

获取查询存储选项

清理空间

删除临时查询

反馈

其他资源

通过

管理查询存储的最佳做法

Azure SQL 数据库中的查询存储默认值

设置最佳查询存储捕获模式

在 Query Store 中保留最相关数据

自定义捕获策略

自定义捕获策略示例

查询存储最大大小

数据刷新间隔（分钟）

修改查询存储默认值

查看查询存储当前设置

示例

查询存储维护

查询存储状态

获取查询存储选项

设置查询存储间隔

查询存储空间使用情况

获取查询存储选项

清理空间

删除临时查询

相关内容

反馈

其他资源