Tuesday, April 2, 2013

Top SQL Queries

select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
highest_cpu_queries.total_logical_reads,
highest_cpu_queries.total_logical_writes,
highest_cpu_queries.execution_count,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 20
qs.plan_handle,
qs.total_worker_time,
qs.total_logical_reads,
qs.total_logical_writes,
qs.execution_count
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
--order by highest_cpu_queries.total_logical_reads desc
--order by highest_cpu_queries.total_logical_writes desc
--order by highest_cpu_queries.execution_count desc

--select top 10 * from sys.dm_exec_query_stats qs