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