SQL Server-Query Store

Query store介绍

Query Store 是一个用来记录和分析查询性能的工具，帮助数据库管理员快速定位和解决性能问题。这种功能对于优化数据库查询和解决性能问题特别有用，是一个让管理变得更加高效的工具
以下是 SQL Server Query Store 功能常见的应用场景：

• 查找最耗资源的查询
  找到在 CPU、I/O、内存等方面最昂贵的查询。
• 获取查询执行的完整历史
  提供查询执行的完整历史记录，便于回溯分析。
• 查询回退与性能回归分析
  如果查询引擎生成的新执行计划比旧计划更差，可以快速定位性能回归的问题，并通过强制使用旧的查询计划（性能更优）来修复问题。
• 查询执行次数统计
  在给定的时间范围内，确定某个查询被执行的次数。
  SQL Server 的 Query Store 是一个基于数据库级别的功能，这意味着可以为每个数据库单独启用，而不是在整个实例级别统一设置。启用方式可以通过 SQL Server Management Studio (SSMS) 图形界面或 T-SQL 命令实现。

Query Store 的主要特点：

• 提供内置报告和动态管理视图 (DMWs)，能够快速高效地分析查询性能。
• 所有版本的 SQL Server 都支持这一功能。
• 在 Azure SQL 数据库中，Query Store 默认启用，无需额外配置。
• 权限要求：
  使用 Query Store 至少需要具备 VIEW DATABASE STATE 权限。这一权限允许用户查看数据库的状态信息，但不会授予更高的管理权限。

use AdventureWorks2008
go
GRANT VIEW DATABASE STATE TO [USER];

• 此权限不能用于强制执行计划或设置刷新间隔，这些操作需 db_owner 角色权限。

设置

在本地 SQL Server 上启用 Query Store，可在 对象资源管理器 中右键单击目标数据库，然后从弹出的菜单中选择 “Properties”
。

从“数据库属性”对话框中的“Select a page”部分中，选择“Query Store”页面：

从Operation Mode（Requested）下拉框中，选择“Read Write”项：

一旦选择了"Read Write"项，Operation Mode（Requested）下拉框下的其他字段将预先填充默认值：

在数据库属性对话框中点击 确定 按钮后，SQL Server Query Store 即被启用，用于捕获查询执行计划和运行时信息。

确认 Query Store 是否已在选定的数据库上启用，可前往 对象资源管理器，刷新并展开该数据库。若启用成功，将看到 Query Store 文件夹及其内置报告列表。

要使用 T-SQL 启用 SQL Server Query Store，请在查询窗口中执行以下语句：

ALTER DATABASE [CriticalManufacturing] SET QUERY_STORE = ON;

Query Store 无法用于 master 和 tempdb，根据microsoft的说明，启用后平均会带来 3-5% 的性能影响。

选项

在数据库属性对话框的 SQL Server Query Store 页面下，第一个选项是 操作模式（实际）（Operation Mode (Actual)）。

此选项是灰色显示的，代表禁用的，无法更改，用于指示 SQL Server Query Store 的状态。
SQL Server Query Store 有三种模式：Off（关闭）、Read Only（只读）和 Read Write（读写）。

• Off：SQL Server Query Store 已关闭。
• Read Only：表示不会跟踪（收集）新的查询运行时统计信息或执行计划。
• Read Write：允许捕获查询的执行计划和运行时统计信息。
  在 Operation Mode (Requested)的下拉框中，可以设置与上述选项相同的模式。此处的设置将直接影响 Query Store 的状态。
  例如，如果在 Operation Mode (Requested)中从下拉框选择了 Read Only 值：
  

这个选项的等效 T-SQL 代码是：

ALTER DATABASE CriticalManufacturing
SET QUERY_STORE = ON   
    (  
      OPERATION_MODE = READ_ONLY   
    );

在 Data Flush Interval (Minutes) 选项中，可以设置一个以分钟为单位的间隔，表示查询运行时统计信息和查询执行计划从 SQL Server 实例的内存刷新到磁盘的频率。默认情况下，此选项设置为 15 分钟。

如果将该选项设置为比刷新频率更低的值，内存中的数据将更频繁地写入磁盘，这可能会对 SQL Server 实例的性能造成负面影响。而如果将该值设置得更高，刷新到磁盘之前会有更多的 SQL Server Query Store信息保留在内存中，但这样也会增加在 SQL Server 重启或崩溃时数据丢失的风险。

以下是用于设置数据刷新间隔（分钟）的 T-SQL 代码：

ALTER DATABASE CriticalManufacturing
SET QUERY_STORE = ON   
    (  
     DATA_FLUSH_INTERVAL_SECONDS = 900   
    );

“Statistics Collection Interval ”选项定义了 SQL Server Query Store中用于聚合查询运行时统计数据的时间间隔。默认情况下，该值设置为 60 分钟。较低的值意味着查询运行时统计数据的粒度更精细，因此会产生更多的时间间隔，这需要更多的磁盘空间来存储查询运行时统计数据。

设置“Statistics Collection Interval ”选项的 T-SQL 代码：

ALTER DATABASE CriticalManufacturing
SET QUERY_STORE = ON   
    (  
    INTERVAL_LENGTH_MINUTES = 1440   
    );

注意，在“Statistics Collection Interval ”选项的 T-SQL 代码中可以设置以下值（以分钟为单位）：1、5、10、15、30、60、1440。
如果在 INTERVAL_LENGTH_MINUTES 中设置了其他数值：

ALTER DATABASE AdventureWorks2014
SET QUERY_STORE = ON   
    (  
    INTERVAL_LENGTH_MINUTES = 900   
    );

执行该语句时会出现如下信息：

Msg 12432, Level 16, State 1, Line 1
SQL Server Query Store Interval length cannot be changed because an invalid value was provided. Please try again with a valid value (1, 5, 10, 15, 30 & 60).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

“Max Size (MB)”选项用于配置 SQL Server Query store的最大大小。默认情况下，SQL Server Query store的最大大小设置为 100 MB。Query store中的数据存储在启用了Query store的数据库中。SQL Server Query store不会自动增长，一旦Query store达到最大大小，其操作模式将自动切换为readonly模式，此时将不再收集新的查询执行计划和查询运行时统计数据。
设置 SQL Server Query Store最大大小的 T-SQL 代码是：

ALTER DATABASE CriticalManufacturing
SET QUERY_STORE = ON   
    (  
    MAX_STORAGE_SIZE_MB = 1024   
    );

“Query Store Capture Mode”选项决定了哪些类型的查询将被捕获到Query Store中。默认情况下，Query Store捕获模式设置为“All”，这意味着在数据库中运行的每个已执行的查询都会存储到 SQL Server Query Store中。

当Query Store Capture Mode设置为“Auto”时，SQL Server Query Store会根据优先级判断要捕获哪些查询，并尝试忽略不常执行的查询和其他临时查询(ad hoc qurey)。此外，“Query Store Capture Mode”的下拉框中还有第三个值“None”。当选择“None”时，SQL Server Query Store将不会收集新的查询信息，但会继续收集之前已记录的查询的信息。

设置此选项的 T-SQL 代码是：

ALTER DATABASE CriticalManufacturing
SET QUERY_STORE = ON   
    (  
    QUERY_CAPTURE_MODE = ALL
    );

“Size Based Cleanup Mode”选项用于在 SQL Server Query Store的大小达到“ Max Size (MB) ”选项的 90% 容量时清理数据。清理过程会移除最旧且开销较低的查询数据，直到Query Store大小降至最大容量的 80% 时停止。默认情况下，此选项设置为“Auto”。

如果在“Size Based Cleanup Mode”下拉框中选择了“Off”值，则当Query Store大小达到最大容量的 90% 时不会执行清理操作，而是当达到最大容量时，Query Store会切换为只读模式（Read Only）。

设置此选项的 T-SQL 代码是：

ALTER DATABASE CriticalManufacturing
SET QUERY_STORE = ON   
    (  
    SIZE_BASED_CLEANUP_MODE = AUTO
    );

“Stale Query Threshold (Days) ”选项用于定义数据在 SQL Server Query Store中保留的时间。默认情况下，该值设置为 30 天。
设置此选项的 T-SQL 代码是：

ALTER DATABASE CriticalManufacturing
SET QUERY_STORE = ON   
    (  
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
    );

通过 T-SQL 可以设置的其他选项之一是 MAX_PLANS_PER_QUERY，该选项未出现在 SQL Server Query Store选项卡中：

ALTER DATABASE CriticalManufacturing
SET QUERY_STORE = ON   
    (  
    MAX_PLANS_PER_QUERY=200
    );

通过此选项，可以设置每个查询在 SQL Server Query Store中存储的最大执行计划数量。默认情况下，每个查询的最大执行计划数为 200 个。

SQL Server Query Store选项卡上的最后一个选项是一个用于清除或清空（clears/purges）Query Store中所有数据的选项，通过点击“Purge Query Data”按钮即可。

或者通过在查询窗口中执行以下 T-SQL 代码可以完成相同的操作：

ALTER DATABASE CriticalManufacturing SET QUERY_STORE CLEAR;

架构与数据收集

SQL Server Query Store会收集query text、query plan和query运行时统计数据，并将其存储在内存中。
需要注意的是，SQL Server Query Store中显示的查询计划是估算执行计划，而非实际执行计划。

• SQL Server Query Store会从查询中提取每个查询语句。例如，如果存储过程包含多个查询语句，SQL Server Query Store会分别存储每个查询语句，并从每个查询语句中获取查询运行时统计数据。
• 如果查询计划和查询文本是新的且唯一的，它们将直接传递给异步写入器（ASYNC Writer），由其写入磁盘。
• 查询运行时统计数据不会直接写入异步写入器。相反，它们将在“Statistics Collection Interval”选项中设置的特定时间间隔内写入。

SQL Server Query Store会将所有信息整合在一起，无论这些信息是存储在磁盘上还是在内存中，并通过内置报表展示出来：

Reports

如本文前面提到的，一旦启用，SQL Server Query Store将开始收集查询运行时统计数据和查询执行计划。内置报表会使用收集到的数据进行分析，并根据报表中的设置以网格或图表形式展示结果。
目前，SQL Server Query Store包含六种内置报表。

Regressed Queries (回归查询)是一种内置报表，用于显示在特定时间范围内（如最近一小时、一天或一周）执行性能指标出现退化的所有查询。
Regressed Queries (回归查询)内置报表分为多个面板。默认情况下，显示最近一小时内排名前 25 的回归查询。
在此可以设置不同选项以查看所需的信息。例如，如果想查看原始数据而不是图表，可以按下按钮以网格格式显示回归查询。

Overall Resource Consumption(总体资源消耗)内置报表显示特定时间段内的资源消耗汇总。默认情况下，结果展示的是上个月的数据，并以四个图表的形式呈现：执行时长（Duration）、CPU 时间（CPU Time）、逻辑读取（Logical Reads）和执行次数（Execution Count）：

要设置额外的图表报表、时间范围和聚合间隔，可以点击“Configure”按钮，随后会弹出“Overall Resource Consumption”对话框，在这里可以为Overall Resource Consumption报表设置不同的选项：

Top Resource Consuming Queries (最高资源消耗查询)内置报表默认显示特定时间段内针对某个数据库资源消耗最多的前 25 个查询。这些资源包括 CPU 时间、内存消耗、物理读取等：

Tracked Queries(跟踪查询) 内置报表，可以跟踪特定查询随时间变化的运行统计信息和执行计划。在“Tracking query”文本框中输入查询 ID（例如 79），然后点击旁边的绿色按钮：

“Queries With Forced Plans 强制计划的查询”内置报告显示了特定查询的所有强制执行计划：

要强制 SQL Server 为特定查询使用特定的执行计划，在回归查询、最高资源消耗查询、变化大的查询或跟踪查询内置报告中，首先选择执行计划 ID，然后点击强制计划按钮：

通过这样做，会强制 SQL Server 对特定查询使用指定的执行计划。这意味着，从现在起每次执行该查询时，SQL Server 都将使用此执行计划，而不会为该查询生成新的执行计划，直到取消强制该计划为止。

要取消强制 SQL Server 对特定查询使用指定的执行计划，可以在Forced Plans, Regressed Queries, Top Resource Consuming Queries, Queries With High Variation or Tracked Queries报告中，选择该执行计划并点击“Unforce Plan”按钮。

这将从“Queries With Forced Plans”报告中移除该执行计划。

“Queries With High Variation高变动查询”内置报告会分析查询，并显示具有最频繁参数化问题的查询：

结论

SQL Server Query Store 是 SQL Server 2016 及更高版本中的一个重要功能。它能记录查询的执行过程（运行时数据）和查询的执行计划，用于监控和分析查询的性能。它还通过内置的报告功能将这些数据直观地展示出来。与以前的 SQL Server 版本相比，这个功能为数据库管理员（DBA）提供了更强大的工具，用于分析和优化数据库性能，摆脱了过去较为繁琐或效率较低的方法。