Collect performance information on high CPU condition in SQL Server

Question

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]
GO
EXEC msdb.dbo.sp_update_alert @name=N'My Test CPU Alert', 
    @message_id=0, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=0, 
    @database_name=N'', 
    @notification_message=N'', 
    @event_description_keyword=N'', 
    @performance_condition=N'Resource Pool Stats|CPU usage %|default|=|5', 
    @wmi_namespace=N'', 
    @wmi_query=N'', 
    @job_id=N'00000000-0000-0000-0000-000000000000'
GO    

Answer 1

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)
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); 

SELECT TOP(144) SQLProcessUtilization AS [SQLServerProcessCPUUtilization], 
               SystemIdle AS [SystemIdleProcess], 
               100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPUUtilization], 
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime] 
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;

Source : Glenn Berry's Script