SQL Server Query Store Settings （查询存储设置）

参考：Query Store Settings - Erin Stellato
        在 SQL Server 2017 中，有九 (9) 个设置与查询存储相关。虽然这些设置记录在sys.database_query_store_options中，但我经常被问到每个设置的值“应该”是多少。我在下面列出了每个设置，以及默认值和更改设置的注意事项。

操作模式

        SQL Server 2016 或 SQL Server 2017 中新数据库或升级数据库的默认值为 OFF。对于 Azure SQL 数据库，默认值为 READ_WRITE。

        如果要启用查询存储，则需要将其设置为 READ_WRITE，这是所需的状态。

        您还可以选择 READ_ONLY，这样就不会捕获新查询、新计划、运行时统计信息和等待统计信息（在 SQL Server 2017 中），但仍会强制执行任何强制计划。如果达到 MAX_STORAGE_SIZE_MB 限制（见下文），则会出现此状态。您可以使用以下查询检查实际状态与所需状态：

SELECT [actual_state_desc], [desired_state_desc]
FROM [sys].[database_query_store_options];
GO

        建议始终以 READ_WRITE 状态运行。我听说过有些环境会在 READ_WRITE 和 READ_ONLY 之间切换。如果您想了解您的工作负载并拥有解决性能问题所需的数据，您需要持续捕获信息。

查询_捕获_模式

        SQL Server 2016 和 SQL Server 2017 的默认值为 ALL。对于 Azure SQL 数据库，默认值为 AUTO。

        使用 AUTO 时，从资源利用率角度来看无关紧要或不经常执行的查询不会被捕获。如果您需要捕获可能只执行几次或使用很少资源的查询，请使用 ALL。否则，请使用 AUTO，因为这将捕获您的大部分工作负载。

        还有第三个选项，NONE，即不捕获任何新查询。对于已存在于查询存储中的查询，将继续捕获运行时和等待统计信息。

        我建议将此选项设置为 AUTO，因为您的环境中需要调整/注意的查询数量只占执行的查询总数的一小部分。如果您排除不使用大量资源或不经常执行的查询，您就不会错过重要数据。

每次查询的最大计划数

        SQL Server 2016、SQL Server 2017 和 Azure SQL 数据库的默认值为 200。

        此设置是一个整数，因此理论上您可以将其设置为 2,147,483,647！如果您不知道查询可能有多少个不同的计划，则可以使用 sys.dm_exec_query_stats 并获取给定 query_hash 的不同 query_plan_hash 值的数量：

SELECT [query_hash], COUNT (DISTINCT [query_plan_hash])
FROM [sys].[dm_exec_query_stats]
GROUP BY [query_hash]
ORDER BY 2 DESC;
GO

        虽然我愿意相信一个查询有 200 个不同的计划确实太多了，但我与几位 DBA 交谈过，他们证实他们有数千个计划。因此，如果您的查询不稳定且生成大量不同的计划，并且您想要捕获每个不同的计划，则可能需要增加此设置。要知道，具有大量查询计划的工作负载将需要更多空间，因此存在限制。您可以将限制设置为低于可能的计划数量以控制大小，但要明白您不会捕获每个计划变体。对于大多数环境来说，200 这个值是一个很好的起点。

最大存储大小_MB

        对于 SQL Server 2016 和 SQL Server 2017，默认值为 100MB。对于 Azure SQL 数据库，默认值特定于层级（基本 = 10MB、标准 = 100MB、高级 = 1GB）。

        查询存储数据存储在用户数据库的内部表中（与其他系统表一样，位于 PRIMARY 文件组中），并通过目录视图公开。您可以配置查询存储可使用的磁盘空间量。

        对于本地解决方案，应增加此设置。对于 SQL 数据库，可能需要增加此设置，有多个因素会影响查询存储数据所需的空间量。这些因素包括：

    1、QUERY_CAPTURE_MODE 的值；如果您捕获所有查询，您将获得比使用 AUTO 时更多的信息。数据量很难预测 - 这取决于您的工作量（您是否有很多只运行一次的查询？您是否有很多使用很少资源的查询？）。

    2、您在查询存储中保留数据的时间长度 (CLEANUP_POLICY)。保留的数据越多，所需的空间就越大。

    3、无论您是否运行 SQL Server 2017 并捕获等待统计信息 (WAIT_STATS_CAPTURE_MODE)。等待统计信息非常有价值，但需要保存和保留的数据更多。

    4、INTERVAL_LENGTH_MINUTES 的值。此值越低，您将拥有的运行时统计数据越多，因此您需要的空间就越多。

    5、工作负载类型。如果您的工作负载为临时工作负载，且查询文本变化较大，那么您将存储更多单个查询，因此将存储更多计划、更多运行时和等待统计信息。如果您的工作负载稳定，且没有临时查询或由动态字符串或 ORM 工具（如 NHibernate 或 Entity Framework）生成的查询，那么您的查询数量和数据总量将更少。

        如您所见，MAX_STORAGE_SIZE_MB 的值应该是多少并没有“答案”。我建议从分配 2GB 开始，然后通过 sys.database_query_store_options 和扩展事件进行监控。对于某些解决方案，1GB 就足够了。对于其他解决方案，您可能需要 5GB 或更多。

        2019 年 5 月 30 日更新： Microsoft 仍未提供任何文档列出 MAX_STORAGE_SIZE_MB 的建议，但是，您可以在 Azure SQL 数据库中将此选项设置为的最大值是 10GB……这表明 Microsoft 可能认为任何大于 10GB 的数据都太大了。这有什么关系？更大的查询存储可能需要更长的时间来加载，并产生更多的开销。您可能必须减少保存数据的时间以使其达到 10GB 的大小。

清理政策（STALE_QUERY_THRESHOLD_DAYS）

        SQL Server 2016、SQL Server 2017 和 Azure SQL 数据库的默认值为 30，但 Azure SQL 数据库的基本层除外，其默认值为 7 天。

        您想保留多少历史数据？如果您是一家从事生产开发的商店，您可能希望保留更多历史记录。如果您的工作量相当稳定，并且您每季度或更少地推出变更，那么 30 天的信息可能对您来说就足够了。您保留的数据越多，您需要的磁盘空间就越多。如果您不确定工作量，我建议从此设置至少 30 天开始，在最初几个月的使用中，您就会弄清楚是否要保留较旧的数据。

基于大小的清理模式

        SQL Server 2016、SQL Server 2017 和 Azure SQL 数据库的默认值为 AUTO，我建议保留此值。

        如果值为 AUTO，当查询存储接近 MAX_STORAGE_SIZE_MB 分配的存储大小时，它将自动清除最旧的数据，以确保有足够的空间容纳新数据。尚未达到 CLEANUP_POLICY 的数据可能会被删除（例如，如果 MAX_STORAGE_SIZE_MB 为 2GB，CLEANUP_POLICY 为 30 天，并且您在 15 天内达到 2GB，则将开始删除数据）。

        您可以将其设置为 OFF，但在这种情况下，如果达到 MAX_STORAGE_SIZE_MB，OPERATION_MODE 将更改为 READ_ONLY，您将不再捕获新数据。建议将其设置为 AUTO，并根据需要调整 MAX_STORAGE_SIZE_MB。

数据刷新间隔秒数

        SQL Server 2016、SQL Server 2017 和 Azure SQL 数据库的默认值为 900（15 分钟）。

        建议保留该值的默认值。

间隔长度分钟

        SQL Server 2016、SQL Server 2017 和 Azure SQL 数据库的默认值为 60。

        这是一个关键设置，因为它决定了运行时统计信息汇总的时间窗口。您只能为该设置选择固定值（1、5、10、15、30、60、1440）。该值越小，您拥有运行时统计信息的时间窗口就越小。这将允许您以更精细的级别查看数据。但是，值越小，您捕获的数据就越多，因此需要的空间就越多。

        对于我支持的客户端环境，我将其设置为 30，因为我喜欢更短的分析时间窗口，并且根据我迄今为止必须排除的性能问题，这是一个很好的窗口。如果您有空间限制或顾虑，请将其保留为默认值 60。

等待统计捕获模式

        SQL Server 2016、SQL Server 2017 和 Azure SQL Database 的默认值为 ON。

        如果您将启用了查询存储的数据库从 SQL Server 2016 升级到 SQL Server 2017，则升级时将启用 WAIT_STATS_CAPTURE_MODE。如果您在 SQL Server 2017 上有一个数据库并启用了查询存储，则将启用此选项。

        如果您使用的是 SQL Server 2017，我建议启用此选项，因为这些信息在排除查询性能故障时非常有价值。请注意，您可能需要增加 MAX_STORAGE_SIZE_MB 以容纳这些额外的数据。