08 September 2015

SQL Top CPU Usage Queries Listing

Copy, paste and execute this query to list the top CPU consuming queries in a SQL DB


use [Master]

select top 20
left(p.cacheobjtype + '(' +p.objtype + ')', 35) as cacheobjtype,
p.usecounts,
p.size_in_bytes / 1024 as size_in_kb,
stat.total_worker_time/1000 as tot_cpu_ms,
stat.total_elapsed_time/1000 as tot_duration_ms,
stat.total_physical_reads,
stat.total_logical_writes,
stat.total_logical_reads,
left (case
        when pa.value=32767 then 'ResourceDb'
        else isnull (db_name (convert (sysname, pa.value)), convert (sysname, pa.value))
        end, 40) as dbname,
sql.objectid,
convert (nvarchar(50), case
        when sql.objectid is null then null
        else replace (replace (sql.[text], char (13), ' '), char(10), '')
        end) as procname,
        replace (replace (substring (sql.[text], stat.statement_start_offset/2 + 1,
case when stat.statement_end_offset = -1 then len (convert (nvarchar(max), sql.[text]))
else stat.statement_end_offset /2 - stat.statement_start_offset / 2 +  1 end),
    char(13), ' '), char(10), ' ') as stmt_text,
    qplan.query_plan
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_plan_attributes (p.plan_handle) pa
inner join sys.dm_exec_query_stats stat on p.plan_handle = stat.plan_handle
outer apply sys.dm_exec_sql_text (p.plan_handle) as sql
outer apply sys.dm_exec_query_plan(p.plan_handle) as qplan
where pa.attribute = 'dbid'
order by tot_cpu_ms desc

select sp.*,st.text
from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) as st
where sp.lastwaittype like 'RESOURCE_SEMAPHORE_QUERY_COMPILE%'
ORDER BY sp.waittime desc;

select q.query_hash,
    q.number_of_entries,
    q.total_time,
    t.text as sample_query,
    p.query_plan as sample_plan
from (select top 20 query_hash,
    count(*) as number_of_entries,
    sum(total_elapsed_time) as total_time,
    min(sql_handle) as sample_sql_handle,
    min(plan_handle) as sample_plan_handle
    from sys.dm_exec_query_stats
    group by query_hash
    having count(*) > 1
    order by count(*) desc) as q
cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p

select top 20 query_hash,
    count(*) as number_of_entries,
    sum(total_elapsed_time) as total_time,
    min(sql_handle) as sample_sql_handle,
    min(plan_handle) as sample_plan_handle
    from sys.dm_exec_query_stats
    group by query_hash
    having count(*) > 1
    order by count(*) desc