数据库占用cpu较高的查询

  近来看到别人的有关数据库查询cpu占用较高的sql语句(本人sql并不好),所以查询了一下资料,记录一下,便于理解和应用。

首先,将语句贴在这里

SELECT TOP 10
  --平均cpu时间 total_worker_time/execution_count AS avg_cpu_cost,
plan_handle, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats ORDER BY [avg_cpu_cost] DESC
 

然后,讲解一下,我对于这段代码的理解

 首先将语句分解,最内层的为:

首先 的意思:将text转化为字符串,然后获取长度。

然后case when else end的意思:

当 statement_end_offset = -1的时候,输出text的长度*2 ,如果不是,输出 statement_end_offset的大小,最终将输出的结果减去statement_start_offset,最终得到结果。/2,就不用说了,大家都懂的。其中的 text 是属于dm_exec_sql_text的,即最终的sql语句存储在这个变量中。

 

然后就是SUBSTRING()函数了。从截图中可以看出,Substring 的用法及其含义了,第一个参数是表达式,然后是截取开始的位置,然后是长度,这样就不难理解上面的语句了。

现在,就是这个模块了首先,需要知道 sys.dm_exec_sql_text(sql_handle) 是什么。

官方解释:  返回由指定的sql_handle标识的SQL批处理的文本。 也就是说,返回执行的sql语句。

那么,现在开始说 sys.dm_exec_query_stats 官方解释:返回缓存的查询计划中的聚合性能统计信息 sql server。缓存计划中的每个查询语句在该视图中对应一行,并且行的生存期与计划本身相关联。从缓存删除计划时,也将从该视图中删除对应行。 备注:若要从我们称之为Azure SQL数据仓库或并行数据仓库,使用名称 sys.dm_pdw_nodes_exec_query_stats。其中上面用到的参数 sql_handle,statement_start_offset,statement_end_offset 都是它产生的。

列名 数据类型 描述
sql_handle
varbinary(64)

表示包含查询的皮查询或存储过程的标记。

sql句柄以及statement_start_offset和语句结束偏移量,可以用于检索查询的sql文本通过调用sys.dm_exec_sql_text动态管理函数。

statement_start_offset int 指示行所说明的查询在其批查询或持久化对象文本中的开始位置(一字节为单位,从0开始)。
statement_end_offset int 指示行所说明的查询在其批查询或持久化对象文本中的结束位置(以字节为单位,从0开始)。之前的版本为sql server 2014,值为-1指示批处理的末尾。不再包括尾随的注释。
total_worker_time bigint 此计划自编译以来执行所用的cpu时间总量(以微妙为单位报告,但仅精确到毫秒)。
execution_count bigint 计划自上次编译以来所执行的次数。
plan_handle varbinary(64)

 表示查询所属的已编译计划的标记。此值可以传递给sys.dm_exec_query_plan动态管理函数来获取查询计划。

当本机编译的存储过程查询内存优化的表时,此项将始终为0x000.

execution_count  bingint  此计划自编译以来所执行的次数。

 

参照上面表格的,我们不难理解初始的语句了。

但是,对于我自己来说,还是有许多的问题的:

 

第一个:为什么start需要/2+1
第二个:len()获取到长度为什么需要*2
第三个:最终的结果为什么要/2
第四个:plan_handle得到的结果如何解读 

请教了别人,得到了答案加上我自己的理解,在此记录一下第四个的答案(前三个有待研究):

 对于第四个问题,看一个这个文章基本就能够明白了:https://www.cnblogs.com/huangxincheng/p/4279870.html 

 

posted @ 2018-02-11 14:42  keke..lele  阅读(4548)  评论(1编辑  收藏  举报