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