Here are a few queries that I’ve collected 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 Consumer — 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
No Comments