SQL Server 2016新特性之 Query Store

SQL Server 2016 新特性之 Query Store

Query Store（查询存储）可帮助您跟踪执行计划（含历史记录）及运行时统计信息。通过该功能，您可以快速查找包含多个计划的新查询，识别无效计划，并强制指定更优的执行计划。

注意：本文所有示例基于 SQL Server 2016 CTP 2.2 版本编写。

启用及配置 Query Store

1. 启用

可以通过数据库属性界面启用：

数据库属性 -> Query Store -> Enable

新数据库属性

也可以使用 T-SQL 启用：

ALTER DATABASE [DEMO_1] SET QUERY_STORE = ON;

2. 配置

单击每个属性以查看其描述。有关每个选项的详细信息，可以在官方文档中找到：

Monitoring Performance by Using the Query Store

查询存储配置

还可以使用 T-SQL 更改 Query Store 配置：

ALTER DATABASE [DEMO_1] 
SET QUERY_STORE (
    OPERATION_MODE = READ_ONLY, 
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367), 
    DATA_FLUSH_INTERVAL_SECONDS = 900, 
    INTERVAL_LENGTH_MINUTES = 60, 
    MAX_STORAGE_SIZE_MB = 100, 
    QUERY_CAPTURE_MODE = AUTO, 
    SIZE_BASED_CLEANUP_MODE = AUTO
);
GO

3. 查看当前磁盘使用情况

左侧显示数据库大小和 Query Store 占比，右侧显示 Query Store 可用和已用大小：

数据库属性 - 查询存储磁盘使用情况

右下方有 Purge Query Data 按钮，可以删除 Query Store 的内容，或使用以下语句之一：

ALTER DATABASE [DEMO_1] SET QUERY_STORE CLEAR ALL;
-- 或
EXEC sys.sp_query_store_flush_db;

Query Store 相关系统对象和扩展事件

在 2016 CTP 2.2 中，包含 6 个新的系统存储过程和 7 个相关目录视图。可以通过运行以下查询找到：

SELECT name, type_desc 
FROM sys.all_objects 
WHERE name LIKE '%query_store%' OR name = 'query_context_settings';

系统对象

存储过程参考：MSDN Library
目录视图参考：MSDN Library

此外，还有 19 个新的扩展事件（Extended Events）：

query_store_background_task_persist_started - Query Store 数据持久化后台任务开始执行时触发
query_store_background_task_persist_finished - Query Store 数据持久化后台任务成功完成时触发
query_store_load_started - Query Store 加载时触发
query_store_db_data_structs_not_released - 在关闭功能时未释放 Query Store 数据结构时触发
query_store_db_diagnostics - 在数据库级别上使用 Query Store 诊断时定期触发
query_store_db_settings_changed - 更改 Query Store 设置时触发
query_store_db_whitelisting_changed - 更改 Query Store 数据库白名单状态时触发
query_store_global_mem_obj_size_kb - 使用 Query Store 全局内存对象大小定期触发
query_store_size_retention_cleanup_started - 启动大小保留策略清除任务时触发
query_store_size_retention_cleanup_finished - 完成大小保留策略清除任务时触发
query_store_size_retention_cleanup_skipped - 基于大小保留策略的清除任务被跳过（不需清除）时触发
query_store_size_retention_query_deleted - 基于大小保留策略从 Query Store 删除查询时触发
query_store_size_retention_plan_cost - 计算计划的驱逐成本时触发
query_store_size_retention_query_cost - 计算查询逐出成本时触发
query_store_generate_showplan_failure - 因为 Showplan 生成失败，Query Store 无法存储执行计划时触发
query_store_capture_policy_evaluate - 在为查询计算捕获策略时触发
query_store_capture_policy_start_capture - 当 UNDECIDED 查询转换捕捉到时触发
query_store_capture_policy_abort_capture - 当 UNDECIDED 查询失败过渡被捕获时触发
query_store_schema_consistency_check_failure - 在 Query Store 架构一致性检查失败时触发

在 SSMS 中查看 Query Store

1. Query Store 目录

启用 Query Store 后，数据库中将新增 Query Store 目录。

SSMS 中的新数据库容器

右键查看信息：

SSMS 查询存储容器

展开目录：

SSMS 查询存储窗格

以下为 2017 版本界面：

2. 顶级资源消耗 SQL

点开“总体资源消耗”查看详细信息：

左图中选中的 SQL 有几个执行计划，右图中就会以不同颜色显示每个计划，气泡的大小取决于总执行次数。可以将鼠标悬停在左侧或右侧图表的对象上，查看特定 query_id 或 plan_id 的详细统计信息。

当您单击不同的计划（3）或（4）时，窗格（5）的底部将显示此特定 plan_id 的执行计划。

根据左侧所选的指标，细节会有所不同：

TOP 资源消费者 - 计划摘要 - 图表视图

再来具体看下面这张图：

Pane Dropdowns 概述

1）Top 可选指标包括：
CPU 时间、查询时间（默认）、执行计数、逻辑读、逻辑写、内存消耗、物理读。

2）左图 - 垂直轴（点小箭头）：

执行次数
Num Plans - 执行计划数量
平均逻辑读次数

3）统计项包括：

平均（默认）、最大、最小、标准差、总计

4）左图 - 水平轴：

查询 ID（默认）
执行次数
平均逻辑读次数

5）右图（计划摘要）垂直轴根据左侧图表中选择的“统计项”变化：

平均（默认）、最大、最小、标准差

6）如果屏幕分辨率较小，会隐藏一些按钮。

点击右侧图表上的“网格”和标题旁边的“垂直视图”，查看可用的按钮：

窗格概述 - 续

可以将“计划摘要”从图表改为表格格式：

Track Query（1）按钮：将打开"Tracked Queries"窗口。
View Query（2）按钮：将使用查询的 T-SQL 脚本打开新的 SSMS 窗口。

配置窗格

“详细网格”按钮（3） 将显示包含所有统计信息的 Top 查询列表（显示更多列）：

详细的网格视图

“网格”按钮（4） 显示 Top 查询列表，但列数将受到限制，显示的列将取决于所选的统计信息和指标：

网格视图

“配置”按钮（5） 允许您在一个位置配置窗格：

配置窗格
配置时间间隔

如果查询有多个执行计划，可以单击左侧图表上的“比较计划”按钮，并排查看计划：

多个计划

可以点击“强制计划”按钮，绑定执行计划：

比较计划

查询 Query Store 视图

查询 Query Store 的已用大小和最大大小：

SELECT current_storage_size_mb, max_storage_size_mb 
FROM sys.database_query_store_options;

在 Query Store 中找查询的 ID：

SELECT q.query_id, t.query_sql_text, OBJECT_NAME(q.object_id) AS parent_object 
FROM sys.query_store_query_text t 
JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id 
WHERE t.query_sql_text LIKE N'%insert %db_store%'
   OR OBJECT_NAME(q.object_id) = 'proc_1';

根据查询 ID、部分查询文本或对象名称（视图、存储过程等）查找计划 ID：

SELECT t.query_sql_text, q.query_id, p.plan_id, OBJECT_NAME(q.object_id) AS parent_object 
FROM sys.query_store_query_text t 
JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id 
JOIN sys.query_store_plan p ON q.query_id = p.query_id 
WHERE q.query_id = 1 
-- OR t.query_sql_text LIKE N'%SELECT c1, c2 FROM dbo.db_store%'
-- OR OBJECT_NAME(q.object_id) = 'proc_1';

找执行计划最多的 Top 10 查询：

SELECT TOP 10 t.query_sql_text, q.query_id, 
    OBJECT_NAME(q.object_id) AS parent_object, 
    COUNT(DISTINCT p.plan_id) AS num_of_plans 
FROM sys.query_store_query_text t 
JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id 
JOIN sys.query_store_plan p ON q.query_id = p.query_id 
GROUP BY t.query_sql_text, q.query_id, OBJECT_NAME(q.object_id)
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY COUNT(DISTINCT p.plan_id) DESC;

找执行次数最多的 Top 10 查询：

SELECT TOP 10 t.query_sql_text, q.query_id, 
    OBJECT_NAME(q.object_id) AS parent_object, 
    SUM(s.count_executions) AS total_executions
FROM sys.query_store_query_text t 
JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id 
JOIN sys.query_store_plan p ON q.query_id = p.query_id 
JOIN sys.query_store_runtime_stats s ON p.plan_id = s.plan_id
WHERE s.count_executions > 1 -- 用于加快查询速度
GROUP BY t.query_sql_text, q.query_id, OBJECT_NAME(q.object_id)
ORDER BY SUM(s.count_executions) DESC;

找受影响的行数最多的 Top 10 查询，这可能有助于检查是否有返回大量行的查询：

SELECT TOP 10 t.query_sql_text, q.query_id, 
    OBJECT_NAME(q.object_id) AS parent_object, 
    s.plan_id, s.avg_rowcount
FROM sys.query_store_query_text t 
JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id 
JOIN sys.query_store_plan p ON q.query_id = p.query_id 
JOIN sys.query_store_runtime_stats s ON p.plan_id = s.plan_id
WHERE s.avg_rowcount > 100
ORDER BY s.avg_rowcount DESC;

找每次执行时编译比例最大的 Top 10 查询。有时查询性能可能会受到过度重新编译的影响，使用它来查找具有大量编译的前 10 个查询：

WITH Query_Stats AS (
    SELECT plan_id, SUM(count_executions) AS total_executions
    FROM sys.query_store_runtime_stats
    GROUP BY plan_id
)
SELECT TOP 10 t.query_sql_text, q.query_id, p.plan_id,
    s.total_executions / p.count_compiles AS avg_compiles_per_plan
FROM sys.query_store_query_text t 
JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id 
JOIN sys.query_store_plan p ON q.query_id = p.query_id 
JOIN Query_Stats s ON p.plan_id = s.plan_id
ORDER BY s.total_executions / p.count_compiles DESC;

其他一些有用的查询可以在 MSDN 网站上找到：

最后 n 次 在数据库上执行的查询
每个查询的执行次数
过去一小时内平均执行时间最长的查询数
在过去 24 小时内具有最大平均物理 IO 读数的查询数，具有相应的平均行数和执行计数
最近在性能上退化的查询（比较不同的时间点）
最近在性能上退化的查询（比较最近与历史执行）
删除即席查询

您可能会发现以下列对自己的查询很有用：

sys.query_store_plan 目录视图中的 is_parallel_plan 列
sys.query_store_runtime_stats 目录视图中的 avg_dop 列
sys.query_store_query 目录视图中的 query_parameterization_type 列
sys.query_store_query 目录视图中的 is_internal_query 列

清理 Query Store 数据

sp_query_store_remove_plan 从 Query Store 中删除特定执行计划（执行计划的运行时统计信息也将被清除）：

EXEC sp_query_store_remove_plan @plan_id = 1;

sp_query_store_reset_exec_stats 可以删除特定执行计划的运行时统计信息，但将执行计划本身保留在 Query Store：

EXEC sp_query_store_reset_exec_stats @plan_id = 1;

sp_query_store_remove_query 从 Query Store 中删除整个查询（包括所有执行计划和统计信息）：

EXEC sp_query_store_remove_query @query_id = 1;

参考资料

说明

本文基于 SQL Server 2016 CTP 2.2 版本编写。Query Store 功能在 SQL Server 2016 正式版（RTM）及后续版本中已稳定提供，部分界面选项或系统视图细节可能随版本更新略有差异，请以官方最新文档为准。

本文地址：https://1diff.fun/archives/sql-server-2016-xin-te-xing-zhi-query-store.html

如果对本文有什么问题或疑问都可以在评论区留言，我看到后会尽量解答。

SQL Server 2016新特性之 Query Store