Your system monitoring tool has been reporting that your server is experiencing high CPU usage. The alerts vary in the forms of “CPU Utilisation is WARNING on host HOSTNAME” or “…the average load in the past 10m > 95%…” They happen at various times throughout the day or on similar times for the past few days. You are interested in finding out what are the possible queries that could be running at that time that caused high CPU utilization. You have considered running the perfmon and a SQL Server trace file to capture the activities and queries that could be running at a specific time that had caused the high CPU utilization. You have wondered if there are alternatives in capturing the possible queries that had caused high CPU utilization and store them in a table and/or sending these queries to yourself and the development team for analysis and performance tuning. The good news is that there is.

Please note that we are not looking for CPU intensive queries (queries that use the most CPU) here. We are looking for the queries that may have caused the CPU Usage to go up to 90 to 100%. Yes. The CPU Usage you see on the Windows Task Manager. I have searched @@CPU_BUSY and @@IDLE as they can possibly lead me to what I am looking for in regards to CPU utilization. No luck. In case you found your way on how to use @@CPU_BUSY and @@IDLE to find the CPU utilization, please do share with me after you had tested the solution successfully.

After spending a few hours researching on the SYS.DM_OS_SYS_INFO and SYS.DM_OS_RING_BUFFERS Dynamic Management Views (DMVs), I have found a few great links (see references) that had helped led me to the following solution:
1. Find the average CPU utilizations for the past 2 minutes. Both the SQL Server processes CPU utilization and the “Other processes CPU” utilization.
2. If they are over 90% then find the top 10 queries that are running now and order them by CPU time. This will capture the possible high CPU utilization queries that lasted more than 2 minutes.
3. You can then store them in a table or send them via email to yourself and/or your development team for analysis and possible tuning.
4. Schedule a SQL Server Agent job to run the code every 3 minutes. Increase the time interval depending on your requirements.

Here’s the TSQL code (please let me know if you have better alternatives):

-- How to find the possible queries that caused high CPU utilization in the past 2 minutes?
-- http://sqlblog.com/blogs/ben_nevarez/archive/2009/07/26/getting-cpu-utilization-data-from-sql-server.aspx
-- http://sqlknowledge.com/2010/12/how-to-monitor-sql-server-cpu-usage-and-get-auto-alerts/

DECLARE @ts_now bigint 
DECLARE @SQLVersion decimal (4,2) -- 9.00, 10.00
DECLARE @AvgCPUUtilization DECIMAL(10,2) 

SELECT @SQLVersion = LEFT(CAST(SERVERPROPERTY('PRODUCTVERSION') AS VARCHAR), 4) -- find the SQL Server Version

-- sys.dm_os_sys_info works differently in SQL Server 2005 vs SQL Server 2008+
-- comment out SQL Server 2005 if SQL Server 2008+

-- SQL Server 2005
--IF @SQLVersion = 9.00
--BEGIN 
--	SELECT @ts_now = cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info 
--END

-- SQL Server 2008+
IF @SQLVersion >= 10.00
BEGIN
	SELECT @ts_now = cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info
END 

-- load the CPU utilization in the past 3 minutes into the temp table, you can load them into a permanent table
SELECT TOP(3) SQLProcessUtilization AS [SQLServerProcessCPUUtilization]
,SystemIdle AS [SystemIdleProcess]
,100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPU Utilization]
,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime] 
INTO #CPUUtilization
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 '%<SystemHealth>%') AS x 
      ) AS y 
ORDER BY record_id DESC


-- check if the average CPU utilization was over 90% in the past 2 minutes
SELECT @AvgCPUUtilization = AVG([SQLServerProcessCPUUtilization] + [OtherProcessCPU Utilization])
FROM #CPUUtilization
WHERE EventTime > DATEADD(MM, -2, GETDATE())

IF @AvgCPUUtilization >= 90
BEGIN
	SELECT TOP(10)
		CONVERT(VARCHAR(25),@AvgCPUUtilization) +'%' AS [AvgCPUUtilization]
		, GETDATE() [Date and Time]
		, r.cpu_time
		, r.total_elapsed_time
		, s.session_id
		, s.login_name
		, s.host_name
		, DB_NAME(r.database_id) AS DatabaseName
		, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,
		((CASE WHEN r.statement_end_offset = -1
			THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
			ELSE r.statement_end_offset
		END - r.statement_start_offset)/2) + 1) AS [IndividualQuery]
		, SUBSTRING(text, 1, 200) AS [ParentQuery]
		, r.status
		, r.start_time
		, r.wait_type
		, s.program_name
	INTO #PossibleCPUUtilizationQueries		
	FROM sys.dm_exec_sessions s
	INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
	INNER JOIN sys.dm_exec_requests r ON c.connection_id = r.connection_id
	CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
	WHERE s.session_id > 50
		AND r.session_id != @@spid
	order by r.cpu_time desc
	
	-- query the temp table, you can also send an email report to yourself or your development team
	SELECT *
	FROM #PossibleCPUUtilizationQueries		
END

-- drop the temp tables
IF OBJECT_ID('TEMPDB..#CPUUtilization') IS NOT NULL
drop table #CPUUtilization

IF OBJECT_ID('TEMPDB..#PossibleCPUUtilizationQueries') IS NOT NULL
drop table #PossibleCPUUtilizationQueries

Once you have results, the queries that have the high cpu time and total elapsed time are the ones you will need to pay close attention. Check for possible missing indexes, outdated statistics, blocking, excessive locking, and possible resource contentions. If multiple jobs are running at the same time, you may want to explore the possibility of modifying their schedules to reduce contention. If newly deployed stored procedures have caused the high CPU utilization, get the development team involved and provide assistance and suggestions to optimizing the queries if and when possible.

REFERENCES:

sys.dm_os_ring_buffers
http://msdn.microsoft.com/en-us/library/cc966540.aspx

sys.dm_os_sys_info
http://msdn.microsoft.com/en-us/library/ms175048(v=sql.105).aspx

Getting CPU Utilization Data from SQL Server
http://sqlblog.com/blogs/ben_nevarez/archive/2009/07/26/getting-cpu-utilization-data-from-sql-server.aspx

How to Monitor SQL Server CPU Usage and get auto alerts
http://sqlknowledge.com/2010/12/how-to-monitor-sql-server-cpu-usage-and-get-auto-alerts/

Be Sociable, Share!

•