如何统计最近最耗时的SQL

2014-11-26 05:16:07 652 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

吉普赛的歌墨衍会员 · AI 创作全网分发关注 + 加入社区

记一次Spark sql3.0任务耗时3小时优化到30分钟总结任务A耗时最近从1个小时逐渐增长为3个小时，导致数据延迟。阅读详情

写了两种，　不过不明白两者哪个更合适，请大家评评，　

--统计最近最耗时的SQL语句

--1. 不用 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)

SELECT TOP 50 

	(select text from sys.dm_exec_sql_text(sql_handle)) as [SQL]

	, CAST( ((qs.total_elapsed_time / 1000000.0)/qs.execution_count) AS DECIMAL(28,2) ) AS [平均消耗秒数]

	, qs.last_execution_time AS [最后执行时间]

	, CAST(qs.last_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最后完成消耗秒数]

	, CAST(qs.min_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最小消耗秒数]

	, CAST(qs.max_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最大消耗秒数]

	, CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [总消耗秒数]

	, (qs.execution_count) AS [总执行次数]

	, creation_time AS [编译计划的时间]

	, qs.*

	from sys.dm_exec_query_stats qs

ORDER BY qs.last_elapsed_time DESC



--2. 用 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)

SELECT TOP 50 

	SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

		((CASE statement_end_offset 

		WHEN -1 THEN DATALENGTH(st.text)

		ELSE qs.statement_end_offset END 

		- qs.statement_start_offset)/2) + 1) as [Statement_SQL]

	, CAST( ((qs.total_elapsed_time / 1000000.0)/qs.execution_count) AS DECIMAL(28,2) ) AS [平均消耗秒数]

	, qs.last_execution_time AS [最后执行时间]

	, CAST(qs.last_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最后完成消耗秒数]

	, CAST(qs.min_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最小消耗秒数]

	, CAST(qs.max_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最大消耗秒数]

	, CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [总消耗秒数]

	, (qs.execution_count) AS [总执行次数]

	, creation_time AS [编译计划的时间]

	, qs.*

	from sys.dm_exec_query_stats qs

		CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY qs.last_elapsed_time DESC

...全文

SQL Server慢SQL排查指南文章目录一、系统表详解1.1 dm_exec_query_stats1.2 dm_exec_sql_text1.3 dm_exec_requests二、问SQL排查常用语句对于SQL Server的慢SQL信息排查，我们可以利用一些系统自带的统计信息表来帮助我们进行问题SQL定位。一、系统表详解 1.1 dm_exec_query_stats 该表记录了SQL Server中相关的执行计划的统计信息，从该表中我们可以从不同的维度分析是否存在一些资源消耗较大的SQL。重点关注字段及其含义： sql_h 阅读详情

吉普赛的歌

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

吉普赛的歌 2014-12-17

打赏
举报

不要看行数=>不要看列数。从内容和总记录数来看都是有区别的

吉普赛的歌 2014-12-17

打赏
举报

引用 21 楼 x_wy46 的回复:

[quote=引用 20 楼 yenange 的回复:] 大哥，不是效率，这个没有用，我要比较的是正确性。

两个sql查询出来的字段有什么不一样吗？[/quote] 不要看行数，看行数，这个如果仔细研究，肯定是有区别的。 --1. 不用 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) 取的是部分SQL --2. 用 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) 取的是全部SQL 不明白为何无一人认真研究这个问题……

专注or全面 2014-12-17

打赏
举报

出了查询文本参数本身被处理，其他有不一样的你给截出来

专注or全面 2014-12-17

打赏
举报

另外 cross apply和子查询当然不完全一样，但是在用这里，效果是完全一样的

专注or全面 2014-12-17

打赏
举报

你要弄清楚 SUBSTRING(qt.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1) 这一段sql是干嘛的？不就是去掉变量的定义，直接揪出来语句本身， (@0 int,@2 int,@3 int)select * from table where [Status] = @0 …… between @2 and @3 变成语句本身 select * from table where [Status] = @0 …… between @2 and @3 查询结果出了这一列的处理，其他的不一样我给他吃了！！！！！！！！！

吉普赛的歌 2014-12-17

打赏
举报

引用 24 楼 yupeigu 的回复:

最好别用这个 (select text from sys.dm_exec_sql_text(sql_handle)) a 因为，这个是完整的语句，而实际上，我们需要的是一个明细的统计信息，比如，你执行了一个存储过程，里面有N多语句，但是哪些语句运行慢，哪些语句运行快呢？通过 cross apply 我们可以找到相应的语句，同时，通过： SUBSTRING(qt.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1) 这段，取出当前存储过程中某一个语句，看他的运行效率。另外，cross apply 和子查询的结果应该是不一样的，cross apply相当于inner join，所以如果没有对应的sql文本，那么就不返回结果。而子查询，会返回所有的 sys.dm_exec_query_stats中的记录，所以是不完全一样的。

比较靠谱，分全给你了……

LongRui888 2014-12-17

打赏
举报

最好别用这个 (select text from sys.dm_exec_sql_text(sql_handle)) a 因为，这个是完整的语句，而实际上，我们需要的是一个明细的统计信息，比如，你执行了一个存储过程，里面有N多语句，但是哪些语句运行慢，哪些语句运行快呢？通过 cross apply 我们可以找到相应的语句，同时，通过： SUBSTRING(qt.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1) 这段，取出当前存储过程中某一个语句，看他的运行效率。另外，cross apply 和子查询的结果应该是不一样的，cross apply相当于inner join，所以如果没有对应的sql文本，那么就不返回结果。而子查询，会返回所有的 sys.dm_exec_query_stats中的记录，所以是不完全一样的。

专注or全面 2014-12-09

打赏
举报

引用 20 楼 yenange 的回复:

大哥，不是效率，这个没有用，我要比较的是正确性。

两个sql查询出来的字段有什么不一样吗？

吉普赛的歌 2014-12-09

打赏
举报

引用 18 楼 x_wy46 的回复:

惭愧，一开始没弄懂你的意思，这里在生产服务器上执行的，你自己看了

大哥，不是效率，这个没有用，我要比较的是正确性。

skysenlin 2014-12-08

打赏
举报

学sql的人啊

吉普赛的歌 2014-12-01

打赏
举报

还有没有高人啊

专注or全面 2014-12-01

打赏
举报

惭愧，一开始没弄懂你的意思，
这里在生产服务器上执行的，你自己看了

专注or全面 2014-12-01

打赏
举报

不好意思，我明白你的意思了，这是sql写法的问题，用在这里，个人感觉没啥不一样的

专注or全面 2014-12-01

打赏
举报

引用 14 楼 yenange 的回复:

[quote=引用 11 楼 x_wy46 的回复:] 我去，你自己只要稍微看一下就知道了 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) 目的是为了查询对应的sql语句我真觉得你没必要纠结是否CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)

基本的东西我还是懂的，我想问的是： (select text from sys.dm_exec_sql_text(sql_handle)) as [SQL] 这个是否合理？[/quote] 什么叫做合理？主要看你想要看什么结果了你统计top sql，不就是为了看看是那些个sql吗，不看文本，光看那些聚合结果有啥用

还在加载中灬 2014-12-01

打赏
举报

你觉得有区别吗? CROSS APPLY的原理也是子查询你想比较什么

吉普赛的歌 2014-12-01

打赏
举报

引用 11 楼 x_wy46 的回复:

我去，你自己只要稍微看一下就知道了 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) 目的是为了查询对应的sql语句我真觉得你没必要纠结是否CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)

基本的东西我还是懂的，我想问的是： (select text from sys.dm_exec_sql_text(sql_handle)) as [SQL] 这个是否合理？

专注or全面 2014-12-01

打赏
举报

以前总是喜欢收集一些统计top的sql，见一个收集一个，越来越多……

后来就总结了一下，这些统计，都是从特定的几个DMV中查询出来的，直接跟不同的对象关联，得到想要的对象

专注or全面 2014-12-01

打赏
举报

我自己的笔记 ************************************************************************* sys.dm_exec_query_stats ************************************************************************* 返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行，并且行的生存期与计划本身相关联。在从缓存删除计划时，也将从该视图中删除对应行。配合sys.dm_exec_query_plan，查询sql缓存的执行计划信息配合sys.dm_exec_sql_text，查询缓存的sql信息

专注or全面 2014-12-01

打赏
举报

我去，你自己只要稍微看一下就知道了 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) 目的是为了查询对应的sql语句执行的信息是从sys.dm_exec_query_stats 中查询得到的然后根据 qs.last_elapsed_time 倒序排序，不知道告诉我你不知道last_elapsed_time 是啥意思真不知道的话，继续看你的sql , CAST(qs.last_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最后完成消耗秒数] 我真觉得你没必要纠结是否CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)

吉普赛的歌 2014-11-27

打赏
举报

木人能解吗？

加载更多回复（8）

如何统计最近最耗时的SQL

相关推荐