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