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/
Some really wonderful information, Sword lily I found this. “Treat your friends as you do your pictures, and place them in their best light.” by Jennie Jerome Churchill.
Good post. Thanks.
Very good info. Lucky me I recently found your site by chance (stumbleupon). I’ve book-marked it for later!
May I simply say what a relief to uncover somebody who truly knows what they’re discussing on the web. You definitely know how to bring a problem to light and make it important. More people need to check this out and understand this side of the story. I was surprised you aren’t more popular given that you certainly have the gift.
Thanks for this post. The code came in very handy. Much respect.
Thank you for the post. Have bookmarked it for further reference.