Description:
Example to show the queries which are using the most CPU,
Check the example below:
------------------------------------------------------------------------------------------------------
select t1.session_id
, t1.request_id
, t1.task_alloc
, t1.task_dealloc
, t2.sql_handle
, t3.text
, t2.statement_start_offset
, t2.statement_end_offset
, t2.plan_handle
from (select session_id
, request_id
, sum(internal_objects_alloc_page_count) as task_alloc
, sum(internal_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage
group by session_id, request_id
) as t1 inner join sys.dm_exec_requests as t2 on t1.session_id = t2.session_id
and t1.request_id = t2.request_id
outer apply sys.dm_exec_sql_text(t2.sql_handle) t3
order by t1.task_alloc desc;
/*
Find query using the most CPU
*/
select highest_cpu_queries.plan_handle
, highest_cpu_queries.total_worker_time
, q.dbid
, q.objectid
, q.number
, q.encrypted
, q.[text]
from (select top 10 qs.plan_handle
, qs.total_worker_time
from sys.dm_exec_query_stats qs
order by qs.total_worker_time desc
) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc;