Sql Server Query Store相关操作

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

ALTER DATABASE [testdb]  

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

    );

/*

查询存储包含3个部分：

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

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

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

*/

/*

查询存储的作用：

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

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

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

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

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

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

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

*/

--固定执行计划

/*

由于统计信息更改、架构更改、索引的创建/删除等多种不同原因，SQL Server 中任何特定查询的执行计划通常会随着时间而改进。过程缓存（其中存储了缓存的查询计划）仅存储最近的执行计划，还会由于内存压力从计划缓存中逐出计划。

因此，执行计划更改造成的查询性能回归可能非常重大，且长时间才能解决。

由于查询存储会保留每个查询的多个执行计划，因此它可以强制执行策略，以引导查询处理器对某个查询使用特定执行计划。 这称为“计划强制”。 查询存储中的计划强制是通过使用类似于 USE PLAN 查询提示的机制来提供的，但它不需要在用户应用程序中进行任何更改。

计划强制可在非常短的时间内解决由计划更改造成的查询性能回归。

*/

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 ;  

sp_query_store_force_plan query_id，plan_id；

--取消固定执行计划

EXEC sp_query_store_unforce_plan query_id，plan_id；