MSSQL 得到执行最耗时的前N条T-SQL语句

最新推荐文章于 2023-01-12 10:56:42 发布

youyoufengling

最新推荐文章于 2023-01-12 10:56:42 发布

阅读量1.9k

点赞数

分类专栏：数据库优化

数据库优化专栏收录该内容

3 篇文章 0 订阅

订阅专栏

适用于SQL SERVER 2005及其以上版本

declare @n int set @n=30
;with maco as 
(
select top (@n) 
plan_handle, 
sum(total_worker_time) as total_worker_time , 
sum(execution_count) as execution_count , 
count(1) as sql_count 
from sys.dm_exec_query_stats group by plan_handle 
order by sum(total_worker_time) desc 
) 
select t.text , 
a.total_worker_time , 
a.execution_count , 
a.sql_count 
from maco a 
cross apply sys.dm_exec_sql_text(plan_handle) t

获取有关按平均CPU时间排在最前面的查询的信息：

SELECT TOP 5 total_worker_time / execution_count AS [Avg CPU Time],
	SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
		((CASE qs.statement_end_offset
			WHEN -1 THEN DATALENGTH(st.text)
			ELSE qs.statement_end_offset
			END - qs.statement_start_offset)/2) + 1) AS statement_text 
 FROM sys.dm_exec_query_stats AS qs 
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st 
 ORDER BY total_worker_time/execution_count DESC