-- 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