SQL Server 2016 查询存储（Query Store）介绍

SQL Server 2016 查询存储（Query Store）介绍

 

英文原文：

https://www.mssqltips.com/sqlservertip/4009/sql-server-2016-query-store-introduction/

 

问题

在所有新的SQL Server 2016数据库引擎特性中，查询存储是当前立即可用的很棒的新增性能调优工具。

 

解决方案

查询存储属性帮助跟踪查询计划、运行时统计信息和查询/执行计划历史。也帮助找出回归查询。你可以快速使用多个执行计划找到新的查询，识别无效执行计划和强制一个更好的计划。

我们将在所有示例中使用最新的SQL Server 2016 CTP 2.2版。

 

启用SQL Server 2016查询存储

查询存储可以使用新的“Query Store”数据库属性页启用和配置：

它也可以使用T-SQL启用。

ALTER DATABASE [DEMO_1] SET QUERY_STORE = ON
GO

 

SQL 2016查询存储配置

一旦你对数据库启用了查询存储，你可以配置其他查询存储设置（可配置设置为粗体并高亮显示）：

在每个属性上点击看到它的描述。你可以在这里找到关于每个配置选项的更详细信息。

查询存储配置也可以使用T-SQL修改：

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

 

SQL Server查询存储属性信息

让我们回顾在“Query Store”属性页可用的其他信息。

当前的磁盘使用（Current Disk Usage）

这部分在左边显示了数据库大小和查询存储使用，在右边显示了查询存储大小和使用：

注意到在“Query Store”页的“Purge Query Data”按钮。你可以使用这个按钮移除查询存储的内容或者使用一下的这些语句之一：

ALTER DATABASE [DEMO_1] SET QUERY_STORE CLEAR ALL
GO

EXEC sys.sp_query_store_flush_db

注意，sp_query_store_flush_db存储过程仍有些BUG，微软可能正在修复。

 

SQL Server查询存储系统对象和新的扩展事件

在SQL Server 2016 CTP 2.2中，有6个新的系统存储过程和7个目录视图与查询存储相关，可以运行如下查询找到：

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

你可以从这里找到存储过程的描述：https://msdn.microsoft.com/en-us/library/dn818153.aspx，这里找到目录视图的描述：https://msdn.microsoft.com/en-us/library/dn818149.aspx

也有19个新的扩展事件：

l query_store_background_task_persist_started – 如果查询存储数据持久化的后端任务开始执行时触发。

l query_store_background_task_persist_finished – 如果查询存储数据持久化的后端任务成功完成时触发。

l query_store_load_started – 当查询存储负载启动时触发。

l query_store_db_data_structs_not_released – 当属性设置为OFF时查询存储数据结构未发布时触发。

l query_store_db_diagnostics – 随着数据库级别的查询存储诊断而周期性触发。

l query_store_db_settings_changed – 当查询存储设置被修改时触发。

l query_store_db_whitelisting_changed – 当查询存储数据库白名单状态被修改时触发。

l query_store_global_mem_obj_size_kb – 随着查询存储全局内存对象大小周期性触发。

l query_store_size_retention_cleanup_started – 当大小保留策略清理任务启动时触发。

l query_store_size_retention_cleanup_finished – 当大小保留策略清理任务完成时触发。

l query_store_size_retention_cleanup_skipped – 当大小保留策略清理任务因为它的最小重复时间还没到而被跳过时触发。

l query_store_size_retention_query_deleted – 当基于大小的保留策略从查询存储删除了一个查询时触发。

l query_store_size_retention_plan_cost – 当对执行计划的驱逐消耗被计算时触发。

l query_store_size_retention_query_cost – 当对执行计划的查询驱逐消耗被计算时触发。

l query_store_generate_showplan_failure – 当查询存储因为showplan生成失败而存储查询计划失败时触发。

l query_store_capture_policy_evaluate – 当对查询的捕获策略被评估时触发。

l query_store_capture_policy_start_capture – 当一个UNDECIDED查询转换为CAPTURED时触发。

l query_store_capture_policy_abort_capture – 当一个UNDECIDED查询转换为CAPTURED失败时触发。

l query_store_schema_consistency_check_failure – 当查询存储架构一致性检查失败时触发。

SQL Server Management Studio（SSMS）查询存储属性

SSMS“Query Store”容器

在启用查询存储后数据库将会在SSMS中有新的“Query Store”容器：

你可以右击“Query Store”容器来查看可用选项：

或者你可以展开容器并使用查询存储SSMS面板：

资源消耗前几名（Top Resource Consumers）

大多数查询存储面板有类似的架构和显示选项。让我们在“Top Resource Consumers”面板示例中查看他们：

面板左侧的图表基于对纵坐标和横坐标选择的指标显示了总的统计数据。也有“Metric”（1）和“Statistics”（2）下拉列表。

面板右侧图表（叫做“Plan Summary”）基于选择的“Metric”（1）和“Statistic”（2）显示了选择的高亮显示的查询（在图表左侧的绿色按钮）的统计信息。对右侧图表选择的“Statistic”成为了一个纵坐标。

左侧图表的刷新按钮将会刷新所有图表。右侧图表的相同按钮将只会刷新右侧图表。

注意到我们选择了带有多个面板的查询用以回顾（左侧图表的“num plans”纵坐标）。右侧图表以不同的颜色显示了每个面板。

你可以将鼠标移过（3）左侧或右侧图表的对象，并看到对特定的query_id或plan_id看到详细的统计信息。

详细信息将根据选择的指标而不同：

在右侧图表中泡的大小（4）依赖于总的执行次数。

当你在面板的底部点击不同的面板（（3）或（4）），将会对特定的plan_id显示执行计划。

注意到一些标题是动态的。面板的名称（6）将会是“Top Resource Consumers [db_name]”。在我们的示例中头部是：“Top 25 Top Resource Consumers During the last hour for database qstore_test”。标题的这些部分是动态的：

l （7） -- “Top 25” – 配置值（下面我们将回顾配置选项）

l （8） -- “last hour” – 配置值

l （9） -- “qstore_test” – 被监控数据库名

让我们回顾“Top Resource Consumers”面板的下拉菜单：

（1） 可用指标：

l CPU Time

l Duration（默认）

l Execution Count

l Logical Reads

l Logical Writes

l Memory Consumption

l Physical Reads

（2） 左侧图表 – 纵坐标：

l “exec count”—执行次数总计

l “num plans”—执行计划数量

l 第三个默认选项是动态的，它基于选择的指标（1）和统计（3）。显示格式是“[statistic][metric]”。

例如：“avg logical reads”。

注意，当“Execution count”指标被选择，然后只有两个选项可用：“exec count”和“num plans”。

（3） 统计：

l Avg（默认）

l Max

l Min

l Std Dev

l Total

（4） 左侧图表 – 横坐标：

l “query id”（默认值）

l “exec count”

l 第三个选项是动态的，它基于选择的指标（1）和统计（3）。显示格式是“[statistic][metric]”。例如：“total logical reads”。注意，当“Execution count”指标被选择，然后只有两个选项可用：“exec count”和“num plans”。

（5） 右侧图表（“Plan Summary”）的纵坐标基于左侧图表中选择的“Statistics”而改变：

l Avg（默认值）

l Max

l Min

l Std Dev

注意，如果在左侧图表“Total”统计被选择，那右侧图表的坐标被设置为“Avg”。这会在最终版本中修改。

（6） 如果你有更小的屏幕分辨率，一些图标会被隐藏。

让我们在右侧图表点击“Grid”和标题旁边的“Vertical View”并回顾可用按钮：

从图表到单元格修改“Plan Summary”允许我们以表格格式对每个计划显示统计信息。

“Track Query”（1）按钮将会打开“Tracked Queries”面板。我们将会在下一步回顾该按钮。

“View Query”（2）将会带着查询的T-SQL脚本打开新的SSMS窗口。

图表左侧的“Detailed Grid”（3）将会显示带有所有统计信息的前几名查询的列表（显示了更多列）：

在左侧图表的“Grid”按钮显示了前几名查询的列表，但列数量会被限制并且显示的列依赖选择的统计信息和指标：

“Configure”按钮（5）允许你统一在一个面板中配置该面板：

l Metric

l Statistics

l Time interval

l 返回多少记录

l Display Time format

这里是“Time Interval”的选项：

如果你有一个带有多个执行计划的查询，你可以在左侧图表点击“Compare Plans”按钮并且并排看到这些面板：

“Force plan”按钮在左侧图表的执行计划部分的下面是可用的。我们将下一步回顾它。

注意：本文基于SQL Server 2016 CTP 2.2而写。一些截屏和行为可能会在最终版本中改变。

接下来

l 很快回来阅读查询存储使用的示例的下一篇文章。

l 下载最新的SQL Server 2016评估板。

l 阅读SQL Server 2016联机帮助文档。

l 阅读如何使用查询存储监控性能。