Statistical SQL statement time-consuming percentage

--SQL statement time-consuming percentage

Declare @tmptb table (ID int,name varchar (), total_worker_time bigint,rate varchar (), 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), '), CHAR (+), ') as Sql_text
From
(
Select
Plan_handle,
SUM (total_worker_time)/Total_worker_time,
SUM (execution_count) as Execution_count,
Count (1) as Cache_count,
(SUM (total_worker_time)/sum (execution_count))/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 < 1
When Avg_time_ms >= and Avg_time_ms <= 2
When Avg_time_ms >= and Avg_time_ms <= 3
When Avg_time_ms >= and Avg_time_ms <= 4
When Avg_time_ms >= and Avg_time_ms <= 5
When Avg_time_ms > 6
Else 7
End
Group_name =
Case
When Avg_time_ms < ' less than 50 milliseconds '
When Avg_time_ms >= and Avg_time_ms <=, then ' 50~200 milliseconds '
When Avg_time_ms >= and Avg_time_ms <= ' 200~500 milliseconds '
When Avg_time_ms >= and Avg_time_ms <= "500~1000 milliseconds"
When Avg_time_ms >= and Avg_time_ms <= 1-3 seconds '
When Avg_time_ms > "More than 3 seconds"
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,rate = total_worker_time * 100/@total_run_time, execute_count from @tmptb the ORDER by ID ASC