You may have situations where the CPU % usage is well below the alert threshold but still queries are running slow because they are waiting for CPU to be available.
This script creates an alert to send out email if the number of queries waiting for CPU exceeds the threshold. Please update the value for the @operator variable to whatever is the email operator you have setup in the SQL Server Agent.
And since I am testing I am using the threshold value of 10. You may want to lower that after testing in your environment.
Lastly, Since I did not want to get bombarded with emails, I am using the 900 seconds (15 minutes) delay of between alert emails. Please feel free to adjust it to your needs.
USE [msdb]
GO
declare @operator varchar(500) -- email operator name
declare @threshold int -- number of queries waiting for the CPU
declare @delay_between_email_alerts int -- this value is in seconds
declare @drop_alert_if_exists bit -- drops and recreates the alert if already exists
-- Assign default values to variables
set @operator = 'DBA'
set @threshold = 10
set @delay_between_email_alerts = 900
set @drop_alert_if_exists = 0
declare @sql_add_alert nvarchar(4000)
declare @sql_add_notification nvarchar(4000)
declare @sql_drop_alert_if_exists nvarchar(4000)
if @drop_alert_if_exists = 1
begin
if exists (select * from msdb..sysalerts where name = 'Alert: Number of processes waiting for CPU exceeded thresold')
EXEC msdb.dbo.sp_delete_alert @name=N'Alert: Number of processes waiting for CPU exceeded thresold'
end
set @sql_add_alert =
'EXEC msdb.dbo.sp_add_alert @name=N''Alert: Number of processes waiting for CPU exceeded thresold'',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=' + cast(@delay_between_email_alerts as nvarchar(10)) + ',
@include_event_description_in=1,
@category_name=N''[Uncategorized]'',
@performance_condition=N''Wait Statistics|Wait for the worker|Waits in progress|>|' + cast(@threshold as nvarchar(10)) + ''',
@job_id=N''00000000-0000-0000-0000-000000000000''
'
print @sql_add_alert
exec(@sql_add_alert)
set @sql_add_notification =
'EXEC msdb.dbo.sp_add_notification
@alert_name=N''Alert: Number of processes waiting for CPU exceeded thresold'',
@operator_name=N''' + @operator + ''',
@notification_method = 1
'
print @sql_add_notification
exec(@sql_add_notification)
go