sql cpu usage query examples

Here's a query that will show the actual database causing high load. It relies on the query cache which might get flushed frequently in low-memory scenarios (making the query less useful).

select dbs.name, cacheobjtype, total_cpu_time, total_execution_count from
    (select top 10
        sum(qs.total_worker_time) as total_cpu_time,  
        sum(qs.execution_count) as total_execution_count, 
        count(*) as  number_of_statements,  
        qs.plan_handle
    from  
        sys.dm_exec_query_stats qs 
    group by qs.plan_handle
    order by sum(qs.total_worker_time) desc
    ) a
inner join 
(SELECT plan_handle, pvt.dbid, cacheobjtype
FROM (
    SELECT plan_handle, epa.attribute, epa.value, cacheobjtype
    FROM sys.dm_exec_cached_plans 
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
     /* WHERE cacheobjtype = 'Compiled Plan' AND objtype = 'adhoc' */) AS ecpa 
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt
) b on a.plan_handle = b.plan_handle
inner join sys.databases dbs on dbid = dbs.database_id

SELECT total_worker_time/execution_count AS AvgCPU  
, total_worker_time AS TotalCPU
, total_elapsed_time/execution_count AS AvgDuration  
, total_elapsed_time AS TotalDuration  
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads 
, (total_logical_reads+total_physical_reads) AS TotalReads
, execution_count   
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1  
, ((CASE qs.statement_end_offset  WHEN -1 THEN datalength(st.TEXT)  
ELSE qs.statement_end_offset  
END - qs.statement_start_offset)/2) + 1) AS txt  
, query_plan
FROM sys.dm_exec_query_stats AS qs  
cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st  
cross apply sys.dm_exec_query_plan (qs.plan_handle) AS qp 
ORDER BY 1 DESC


This will get you the queries in the plan cache in order of how much CPU they've used up. You can run this periodically, like in a SQL Agent job, and insert the results into a table to make sure the data persists beyond reboots.


When you read the results, you'll probably realize why we can't correlate that data directly back to an individual database. First, a single query can also hide its true database parent by doing tricks like this:

USE msdb
DECLARE @StringToExecute VARCHAR(1000)
SET @StringToExecute = 'SELECT * FROM AdventureWorks.dbo.ErrorLog'
EXEC @StringToExecute

declare @ts_now bigint
select @ts_now =  cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info --SQL Server 2008
--select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info --SQL Server 2005

select top 50 record_id,
    dateadd(ms, -1 * ((@ts_now - [timestamp])), GetDate()) as EventTime,
    SQLProcessUtilization,
    SystemIdle,
    100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (
    select
          record.value('(./Record/@id)[1]', 'int') as record_id,
          record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
          record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
          timestamp
    from (
          select timestamp, convert(xml, record) as record
          from sys.dm_os_ring_buffers
          where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
          and record like '%%') as x
    ) as y
order by record_id desc