-- sql语句耗时百分比

declare @tmptb table(id int,name varchar(50),total_worker_time bigint,rate varchar(50),execute_count bigint)

;with cte1 as
(
select a.*,t.*
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.plan_handle) t
where t.dbid >= 5 
)
,cte2 as
(
select 
t.dbid,db_name(t.dbid) as dbname, 
a.total_worker_time,a.avg_time_ms,a.execution_count,a.cache_count,
replace(replace(t.text,CHAR(10),' '),CHAR(13),' ') as sql_text
from 
(
select 
plan_handle,
sum(total_worker_time) / 1000 as total_worker_time , 
sum(execution_count) as execution_count , 
count(1) as cache_count,
(sum(total_worker_time) / sum(execution_count) ) / 1000 as avg_time_ms
from cte1 
where dbid >= 5
group by plan_handle 
) a 
cross apply sys.dm_exec_sql_text(a.plan_handle) t
)
,cte3 as
(
select 
group_id = 
case 
when avg_time_ms < 50 then 1
when avg_time_ms >= 50 and avg_time_ms <= 200 then 2
when avg_time_ms >= 200 and avg_time_ms <= 500 then 3
when avg_time_ms >= 500 and avg_time_ms <= 1000 then 4
when avg_time_ms >= 1000 and avg_time_ms <= 3000 then 5
when avg_time_ms > 3000 then 6
else 7
end,
group_name = 
case 
when avg_time_ms < 50 then '小于50毫秒'
when avg_time_ms >= 50 and avg_time_ms <= 200 then '50~200毫秒'
when avg_time_ms >= 200 and avg_time_ms <= 500 then '200~500毫秒'
when avg_time_ms >= 500 and avg_time_ms <= 1000 then '500~1000毫秒'
when avg_time_ms >= 1000 and avg_time_ms <= 3000 then '1~3秒'
when avg_time_ms > 3000 then '大于3秒'
else 'unknown'
end,
-- sum(total_worker_time) as total_run_time ,
*
from cte2 
)

insert into @tmptb(id,name,total_worker_time,execute_count)
select 
group_id, group_name,sum(total_worker_time) as total_worker_time,sum(execution_count) as execute_count
from cte3 
group by group_id,group_name

declare @total_run_time bigint
select @total_run_time = sum(total_worker_time) from @tmptb

select id,name,total_worker_time as '总时间','比率' = total_worker_time * 100 / @total_run_time,execute_count as '执行次数' from @tmptb order by id asc