欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

利用sys.dm_exec_query_stats实现查找并优化SQL语句

程序员文章站 2022-06-28 18:16:01
今天在看sql server 2012 的新特性,当看到某一条时,居然发现了sys.dm_exec_query_stats 视图进行了升级;又由于该试图一直在用,并且相当的有用,可以说是查找并优化s...

今天在看sql server 2012 的新特性,当看到某一条时,居然发现了sys.dm_exec_query_stats 视图进行了升级;又由于该试图一直在用,并且相当的有用,可以说是查找并优化sql 语句的一大利器。所以,今天特做下记录。

msdn 上对sys.dm_exec_query_stats 视图的定义:返回 sql server 2012 中缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行,并且行的生存期与计划本身相关联。在从缓存删除计划时,也将从该视图中删除对应行。

其实说白了,该视图存放的就是当前所有执行计划的详细信息,比如某条执行计划共占cpu多少等等。因为该视图对编译次数、占用cpu资源总量、执行次数等都进行了详细的记录,所以,可以说是优化 db服务器cpu 的一大利器。

由于该试图是动态的,所以并一定总是准确,也可能某条执行计划在查询的时间做了重编译,得到了偏差的信息等;另外,对于sys.dm_exec_query_stats 中占用资源最多的,并不一定是有性能问题的,要同时观察执行次数 和 io 读写等,而对于执行过于频繁的,则要考虑在程序中加缓存了;该系统试图不能用作应急优化用,但是日常优化,一定要做一个重要的参考指标。

说了这么久,下面放 优化的sql :

select s2.dbid,
    (select top 1 substring(s2.text,statement_start_offset / 2+1 ,
      ( (case when statement_end_offset = -1
         then (len(convert(nvarchar(max),s2.text)) * 2)
         else statement_end_offset end)  - statement_start_offset) / 2+1))  as sql_statement,
    execution_count,     plan_generation_num,     last_execution_time,       total_worker_time,     last_worker_time,     min_worker_time,
    max_worker_time,    total_physical_reads,     last_physical_reads,
    min_physical_reads,      max_physical_reads,      total_logical_writes,     last_logical_writes,     min_logical_writes,     max_logical_writes
from sys.dm_exec_query_stats as s1
cross apply sys.dm_exec_sql_text(sql_handle) as s2 
where s2.objectid is null
order by s1.total_worker_time desc

具体列的含义,请参考文末。

接下来说下 sql server 2012 对sys.dm_exec_query_stats 试图的增强功能吧:添加了四列,以帮助排除长时间运行的查询所存在的问题。可以使用 total_rows、min_rows、max_rows 和 last_rows 聚合行计数列,分隔那些从出现问题的查询(可能缺少索引或查询计划出错)中返回大量行的查询。

具体意思,从名称中就不难看出来;经过本人的试用之后,却发现这个改进对于某些执行计划并不是很实用,为什么呢,因为执行计划是可能接受参数的,所以行数的数量和参数密切相关,所以,对于返回行数和参数密切相关的执行计划,这个改进没有什么用,反之,还是有一定参考作用的。