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

2 responses to “SQL Server Alerts for CPU Utilization Alert

  1. really good post – helped me a lot! What about a post about SQL Jobs? I have recently managed to get one and found this site to be extremely useful. Worth a try: SQL Jobs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s