Query Store是SQL Server 2016中引入的、语句级别的性能监控和调优工具，它不仅自动捕获查询（Query）、执行计划（Plan）、运行时统计信息（Runtime）和等待（Wait）统计的信息，而且还可以识别出由于执行计划更改而导致的性能差异，简化了性能故障排除的流程，降低了性能优化的难度。从字面上来解释，Query Store是“查询的仓库”，它是由SQL Server引擎自动维护，用于捕获数据库中执行的查询，以及跟其执行性能相关联的数据。不同于DMV把数据存储到内存中，Query Store会把捕获的性能数据存储到文件（Disk）中，为了最优化数据的写入，Query Store使用异步更新方式，新捕获的数据会存储到内存中，每隔一定时间（分时）就把捕获的数据存储到硬盘（Disk）中。

一，启用Query Store

Query Store默认是关闭的，启用Query Store对查询性能还是有一定的影响的，

 View Code

参数注释：

  • CLEAR：清空Query Store的数据
  • OPERATION_MODE：READ_WRITE是指Query Store会持续收集和持久化数据，而READ_ONLY是指只能从Query Store读取信息，而不会更新Query Store。
  • CLEANUP_POLICY：定义数据留存的时间窗口，超过该窗口，过期的数据从Query Store中清理出去。
  • DATA_FLUSH_INTERVAL_SECONDS：定义数据持久化到硬盘的频率，默认值是900s（15min）。为了优化性能，Query Store收集的数据采用异步写方式，每隔一定的时间会把捕获的数据写到硬盘中。
  • MAX_STORAGE_SIZE_MB：设置Query Store的最大存储空间，如果Query Store达到最大存储空间的限制，Query Store会把操作模式（OPERATION_MODE）更改为READ_ONLY，不再写入新的数据。
  • INTERVAL_LENGTH_MINUTES：每隔一定的时间窗口对运行时的执行统计数据进行聚合，然后把聚合值存储到Query Store中。
  • SIZE_BASED_CLEANUP_MODE ：基于Query Store占用的空间大小控制是否启动清理程序，清理程序会自动删除Query Store中过时的数据，以释放Query Store的空间
  • QUERY_CAPTURE_MODE ：定义捕获查询的捕获模式，默认值是ALL，表示捕获所有的查询，AUTO表示基于执行次数和资源消耗来捕获相关的查询。
  • MAX_PLANS_PER_QUERY：定义为每个查询维护的计划数量，默认值是200
  • WAIT_STATS_CAPTURE_MODE ：是否捕获等待统计（wait stats），从SQL Server 2017(14.x)开始支持该选项。
  • QUERY_CAPTURE_POLICY：定义捕获Query的策略，
    • STALE_CAPTURE_POLICY_THRESHOLD ：定义评估的时间间隔（evaluation interval period ），根据以下的选项来确定是否一个query应该被捕获，evaluation period的默认值是1day
    • EXECUTION_COUNT：默认值是30，在evaluation period内，如果一个query的执行次数超过指定的数值，那么捕获该query。
    • TOTAL_COMPILE_CPU_TIME_MS：默认值是1000ms，在evaluation period内，如果一个query的编译时间超过指定的时间，那么捕获该query。
    • TOTAL_EXECUTION_CPU_TIME_MS：默认值是100，在evaluation period内，如果一个query的执行时间超过指定的时间，那么捕获该query。

例如，使用以下的脚本来启用TestDB的Query Store：

复制代码
ALTER DATABASE [TestDB]  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        ) );
复制代码

在启用Query Store之后，用户可以通过系统视图来查看各个选项的配置情况：

sys.database_query_store_options

当然，也可以使用SSMS对Query Store的各个选项进行配置和查看：

二，Query Store捕获的信息

从总体上来说，Query Store包含四个Store，分别是query store、plan store、runtime stats store和wait stats store：

  • query store 用于捕获查询的信息，
  • plan store用于捕获执行计划的信息，
  • runtime stats store用于捕获执行计划的变更记录和统计信息等，