Logo

菜单

daicy
发布于 2022-08-25 / 1038 阅读
0
0

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. 配置

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

https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017#Options

查询存储配置

还可以使用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'

系统对象

此外，还有19个新的扩展事件：

  • query_store_background_task_persist_started - Query Store数据持久化后台任务开始执行，则触发
  • query_store_background_task_persist_finished - Query Store数据持久化后台任务成功完成，则触发
  • query_store_load_started - Query Store load时触发
  • 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 - 启动size保留策略清除任务时触发。
  • query_store_size_retention_cleanup_finished - 完成size保留策略清除任务时触发。
  • query_store_size_retention_cleanup_skipped - 基于size保留策略的清除任务被跳过（不需清除）时触发
  • query_store_size_retention_query_deleted - 基于size保留策略从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. top 资源消耗sql

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

https://www.mssqltips.com/tipimages2/4009_ChartViewAll.gif

左图中选中的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) 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 -- used to make the query faster
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 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

参考

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

https://www.mssqltips.com/sqlservertip/4047/sql-server-2016-query-store-queries/

文章知识点与官方知识档案匹配，可进一步学习相关知识

MySQL入门技能树SQL高级技巧CTE和递归查询6860 人正在系统学习中

SimpleJdbcCall
druid 介绍

评论