本文属于Azure SQL DB/DW系列
上一文:Azure SQL DB/DW 系列(7)——Query Store案例(4)——查找参数化问题
本文开始整理和总结Query Store的技术要点,以更详细的方式简介Query Store
Query Store优势
Query Store默认是关闭的,一旦开启,它就会收集数据库中查询的汇总信息和工作负载的统计信息。这些数据可以用来处理问题,也可以用来建立性能基线。
本文会做一个技术简介,如果从头看到现在,那么应该对Query Store有个初步的了解,所以是时候再重新介绍一下Query Store。
很多地方都说,Query Store像是一个飞行记录盒,存储了SQL Server的诸如运行时间,读写,CPU等信息,也包含了查询计划的信息。从SQL 2017开始,Query Store也可以使用于自动计划修正等功能。
Query Store的用处
建立性能基线
首先,由于其收集了大量的运行信息,可以很容易地对这些实际数据进行一个性能基线的制定。比如“总体资源消耗”这个报表,可以看到各种指标。这个报表是建立性能基线的其中一个绝佳数据来源。
如果需要定制化一些数据,也可以使用下面的目录视图来查询:
- sys.query_store_runtime_stats:运行时间的汇总数据。
- sys.query_store_query_text:在数据库中执行过的sql文本,每个相同的文本只会存储一次。
- sys.query_context_settings:存储查询使用的配置内容。如SET ANSI_NULLS ON等。
- sys.query_store_query:存储基于文本唯一执行的所有查询,并执行上下文设置,以便你可以跟踪它们。
- sys.query_store_plan:以XML格式显示查询的预估执行计划和执行时间统计等。
- sys.query_store_runtime_stats_interval:存储Query Store的时间间隔。
更多的介绍会在后续再说。
稳定性能
上图中的第一个报表是“回归的查询”,前面
Azure SQL DB/DW 系列(5)——Query Store案例(2)——计划回归中提到计划回归的内容,通过这个报表,可以看到存在计划回滚的查询,计划回归非常有可能导致性能下降,这个报表可以显示出每个查询的执行计划,默认存储最近的一个月。
通过前面提到的两个报表,可以看到哪些查询的执行计划是有问题的。在Query Store出现之前,需要用计划向导来修复这类问题,但是通过Query Store可以不修改代码的前提下实现同样的功能。
计划回归就是SQL Server编译一个以前已经执行过的查询(已有计划缓存)并且这次的执行计划不如之前的那个,如果手动强制,需要小心带来一些非预期的问题。如果索引和结构变化,甚至会导致执行报错。
另外一个报表“具有高度差异的查询”,显示哪些查询在性能上不一致,这为你提供了一种方法,告诉哪些查询有时执行不同,并可能需要调整以便持续执行。
没有Query Store的处理方法
在Query Store还没出现或者由于某种原因即使是新版本也没用到它的时候,你可能需要用其他工具来进行问题侦查和处理。比如:
- SQL Server Profiler/SQL trace:它很有用,但是开销巨大,用过的人应该都有感觉。微软早已不建议使用它们,不过对于某些情况下,它们还是有不可替代的作用,比如一个没有很好管理的环境,Default trace则是没办法时的办法。
- Extended Events:从SQL 2008开始引入的以替代Profile/Trace为目标的轻量级工具,它非常强大,在后续也会被一直强化,是服务器端处理问题的首要推荐工具之一,不过Azure环境下配置有些麻烦,特别是在完善的权限体系下,非高权限用户配置起来可能做不到。另外不合理地使用 Extended Events同样也会造成服务器压力增大。
- Trace Flag:跟踪标记,很多功能只有跟踪标记可以实现,特别是服务器级别的配置。这里不多做介绍,因为这个非常专业。
- DMVs:DMVs作为使用SQL 命令进行问题查询是非常有用的,可以查看很多当前问题,但是它们以累积值为主,所以并不是非常好普及,不过个人经验中,还是主要以DMVs工具为主。比如在SQL 2019中,开启了Trace Flag 2451这个跟踪标记之后,可以用下面的DMV查询来查看正在运行的会话中,最近一次运行的实际执行计划:
SELECT er.session_id,
er.start_time,
er.status,
er.command,
st.text,
qp.query_plan AS cached_plan,
qps.query_plan AS last_actual_exec_plan
FROM sys.dm_exec_requests AS er
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan_stats(er.plan_handle) qps
WHERE session_id > 50
AND status IN ('running', 'suspended');
GO
DMV工具很多,这个需要长期积累,不过记住他们是基于内存的,一旦服务器重启或者内存被清空,则数据会消失。
还有一些国外专家多年积累后编写出来的工具,其中一个我用了4年的非常强大的工具: sp_whoisactive,只能推荐用于非云版本的服务器中,可以自行网上搜索源码。
除了上面提到的之外,还有诸如Windows的性能计数器,SQL Server的等待信息,Linux的一些第三方工具等。
Query Store如何改变这种现象
Query Store首先把数据收集在一起,同时提供了多种纬度的分析,在2019中可以按下面的纬度来统计:
- 执行次数
- 持续时间
- CPU时间
- 逻辑读
- 逻辑写
- 物理读
- 物理写
- CLR时间
- DOP:并行度
- 内存消耗
- 行数
这些信息可以是总计、平均值、最大最小值和标准偏差。通过Query Store,我们可以用来处理下面常见的问题: - 查找和修复回归的查询
- 查找和标识最高的资源消耗查询。
- A/B测试
- 升级SQL Server版本时的性能测试
- 查找需要优化的查询。
另外从SQL 2017开始(Azure已整合),引入了自动计划修正(Automatic Plan Correction)功能,可以基于计划回归自动强制计划或者不强制计划。
总结
Query Store提供了自动地,简单地收集SQL Server性能数据,可用于处理多种性能问题,接下来的几篇文章里面会对这些内容做扩展介绍。