SQL Server Alerts for CPU Utilization Alert

It isn’t immediately obvious when looking at SQL Server’s built-in alerting mechanism how you can set-up alerts and automation for objects outside of the SQL Server service. But SQL Server agent alerts does expose the WMI interface that you can use to query for events. I don’t personally find it to be a very user-friendly mechanism and it seems like you actually need to query for events in WMI as opposed to just straight interrogation of the Windows objects themselves. But, nonetheless, since WMI is a well-known interface into Windows, you can use WMI to capture alerts that are occuring outside of SQL Server to do things like, say, change thresholds on runaway queries by altering Resource Governor settings when a threshold is breached on CPU utilization.

Something like that would need to look at the Processor object events in WMI. The way that I have created these for SQL Server alerts is by using PowerShell first to test out the queries. With the Get-WmiObject scriplet in PowerShell, it is easy to test things out in PowerShell first.

Then you can build out a SQL Agent alert like this:

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N’WMITest’,
  @message_id=0,
  @severity=0,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @category_name=N'[Uncategorized]’,
  @wmi_namespace=N’\\.\ROOT\CIMV2′,
  @wmi_query=N’SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA “Win32_Processor” AND TargetInstance.LoadPercentage > 10′,
  @job_id=N’00000000-0000-0000-0000-000000000000′
GO

 

Advertisements