:::: MENU ::::

Top 50 queries with sql text and plan

The script below shows you the top 50 queries with sql text and plan order by total worker time.

Total worker time is measured in cpu time it has used since compilation, so even a relatively simple plan that runs fast but very often can have a high total worker time.

select
eqp.query_plan,
highest_cpu_queries.total_worker_time,
db_name(q.dbid) as dbname,
q.objectid,
highest_cpu_queries.execution_count,
highest_cpu_queries.last_execution_time,
highest_cpu_queries.creation_time,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time,
qs.last_execution_time,
qs.execution_count,
qs.creation_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
cross apply sys.dm_exec_query_plan(plan_handle) eqp
order by highest_cpu_queries.total_worker_time desc

//R


So, what do you think ?