Sql Server query store

     一、querystore 自sql server 2016 版本开始提供，查询存储包含3个部分：

1.计划存储：用于保存执行计划信息；

2.运行时统计信息存储： 用于保存执行统计信息；

3.等待统计信息存储： 用于保存等待统计信息；

二、查询存储的作用：

1.快速查找并修复通过强制使用先前查询计划而造成的计划性能回归。 修复近期由于执行计划更改而出现性能回归的查询；

2.确定在给定时间窗口中查询执行的次数，从而帮助 DBA 对性能资源问题进行故障排除；

3.标识过去 x 小时内的前 n 个查询（按执行时间、内存占用等);

4.审核给定查询的查询计划历史记录;

5.分析特定数据库的资源（CPU、I/O 和内存）使用模式;

6.确定资源上正在等待的前 n 个查询;

7.了解特定查询或计划的等待性质;

  三、使用查询存储固定执行计划:

由于统计信息更改、架构更改、索引的创建/删除等多种不同原因，SQL Server 中任何特定查询的执行计划通常会随着时间而改进。过程缓存（其中存储了缓存的查询计划）仅存储最近的执行计划，还会由于内存压力从计划缓存中逐出计划。因此，执行计划更改造成的查询性能回归可能非常重大，且长时间才能解决。由于查询存储会保留每个查询的多个执行计划，因此它可以强制执行策略，以引导查询处理器对某个查询使用特定执行计划。 这称为“计划强制”。 查询存储中的计划强制是通过使用类似于 USE PLAN 查询提示的机制来提供的，但它不需要在用户应用程序中进行任何更改。计划强制可在非常短的时间内解决由计划更改造成的查询性能回归。

固定查询执行计划：

exec sp_query_store_force_plan query_id，plan_id；

取消固定执行计划：

exec sp_query_store_unforce_plan query_id，plan_id；

四、查看查询存储中捕获的sql 统计信息，包括sql 文本、执行计划、消耗资源状况

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.* 
FROM sys.query_store_plan AS Pl 
INNER JOIN sys.query_store_query AS Qry 
     ON Pl.query_id = Qry.query_id 
INNER JOIN sys.query_store_query_text AS Txt 
     ON Qry.query_text_id = Txt.query_text_id ;

五、开启查询存储(建议打补丁KB 4340759 或更新到sql server 2016 sp2 cu2 以上以解决在繁忙的数据库中开启查询存储导致服务器性能下降的问题)

  通过sql 开启：

ALTER DATABASE [db_name]

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       --统计信息收集间隔时间

    );

通过sql server management studio 开启：

六、通过sql server management studio 图形化界面使用查询存储，方便dba查找top sql 以进行优化，找出并解决由于执行计划选择错误导致性能衰退的查询sql：