Take the 2-minute tour ×

I have a SQL server cluster that is often reaching 100% CPU out of working hours. I want to gather some information to help troubleshooting when the actual condition is taking place.

I'm thinking of gathering info from sys.dm_exec_requests for example.

Question: How can i trigger this information gathering to take place based on CPU utilization condition

For Example: Insert the results of sys.dm_exec_requests into a table when CPU > 95%

suggestions for anything else I should gather are very welcome!

It appears that I might be able to do this with alerts, but I need to better understand the counters as my example doesn't fire, despite CPU >5%

USE [msdb]
EXEC msdb.dbo.sp_update_alert @name=N'My Test CPU Alert', 
    @performance_condition=N'Resource Pool Stats|CPU usage %|default|=|5', 
share|improve this question

migrated from stackoverflow.com Oct 6 '12 at 4:24

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 4 down vote accepted

I think we might need to dip into sys.dm_os_ring_buffers and sys.dm_os_sys_info to get the CPU utilization data.

Please find below the script of Glenn Berry

-- Recent CPU Utilization History (SQL 2008/2008 R2 Only)

SELECT TOP(144) SQLProcessUtilization AS [SQLServerProcessCPUUtilization], 
               SystemIdle AS [SystemIdleProcess], 
               100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPUUtilization], 
            AS [SystemIdle], 
            AS [SQLProcessUtilization], [timestamp] 
      FROM ( 
            FROM sys.dm_os_ring_buffers 
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
      ) AS y 
ORDER BY record_id DESC;

Source : Glenn Berry's Script

share|improve this answer
This is really useful. I guess I could run this query periodically and add some conditional logic the invokes a second information gathering step if CPU untilisation rises above a specified level –  reticentKoala Oct 5 '12 at 11:55
Glad you found it useful. FYI, if it helped you could rate the answer and/or mark it as an answer! –  vmvadivel Oct 5 '12 at 12:29

Your Answer


