IT Giant

Here are a few quer­ies that I’ve col­lec­ted that can be used to find out any CPU related issues.

1. Top 20 HIGH CPU batches.

SELECT TOP 20 (total_worker_time * 1.0) / 1000000 AS CPU_Utilized_in_Seconds, convert(varchar(255), text) as TEXT
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
order by (total_worker_time * 1.0) / 1000000 desc

2. Top 20 HIGH CPU statements.

SELECT TOP 20 convert(varchar(255), SUBSTRING(b.text, (a.statement_start_offset/2) + 1,
((CASE statement_end_offset  WHEN 
-1 THEN DATALENGTH(b.text)          
ELSE a.statement_end_offset 
END               
- a.statement_start_offset)/2) + 1)) AS statement_text,
         c.query_plan,        
          total_worker_time as CPU_time   
           FROM sys.dm_exec_query_stats a  
           CROSS APPLY sys.dm_exec_sql_text (a.sql_handle) AS b  
           CROSS APPLY sys.dm_exec_query_plan (a.plan_handle) AS c  
           ORDER BY total_worker_time DESC

3. CPU usage history

        DECLARE @ts_now BIGINT
        SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
        
        SELECT 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

4. TOP 50 CPU Resource Con­sumer — Objects Order

SELECT  TOP 50
			[Database] = DB_NAME(st.dbid),
            [Object_Name] = object_name(st.objectid,st.dbid),
            total_cpu_time = SUM(total_worker_time / 1000),
            execution_counts = MIN(execution_count),
            avg_cpu_time = SUM((total_worker_time / execution_count) / 1000),
            min_cpu_time = SUM(min_worker_time / 1000),
            max_cpu_time = SUM(max_worker_time / 1000),
            last_cpu_time = SUM(last_worker_time / 1000),
            total_time_elapsed = SUM(total_elapsed_time / 1000 ),
            avg_time_elapsed = SUM((total_elapsed_time / execution_count) / 1000),
            min_time_elapsed = SUM(min_elapsed_time / 1000),
            max_time_elapsed = SUM(max_elapsed_time / 1000),
            avg_physical_reads = SUM(total_physical_reads / execution_count),
            avg_logical_reads = SUM(total_logical_reads / execution_count)
FROM
            sys.dm_exec_query_stats qs
CROSS APPLY
            sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
            object_Name(st.objectid,st.dbid) IS NOT NULL
            AND st.dbid > 5
            --AND db_name(st.dbid) = 'VolCap'
GROUP BY
			object_name(st.objectid,st.dbid),
			st.dbid
ORDER BY
            total_cpu_time DESC