Here is the code I am using to send email alerts. It seems to send more false +ves, can anyone guide me how to resolve this ?
DECLARE @Waiting INT; DECLARE @Message NVARCHAR(MAX); SELECT @Waiting = COUNT( s.session_id ) FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON ( s.session_id = c.session_id ) LEFT OUTER JOIN sys.dm_exec_requests r ON ( s.session_id = r.session_id ) LEFT OUTER JOIN sys.dm_os_tasks t ON ( r.session_id = t.session_id AND r.request_id = t.request_id ) LEFT OUTER JOIN ( -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as -- waiting for several different threads. This will cause that thread to show up in multiple rows -- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread, -- and use it as representative of the other wait relationships this thread is involved in. SELECT * ,ROW_NUMBER() OVER ( PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC ) AS row_num FROM sys.dm_os_waiting_tasks ) w ON ( t.task_address = w.waiting_task_address ) AND w.row_num = 1 LEFT OUTER JOIN sys.dm_exec_requests r2 ON ( r.session_id = r2.blocking_session_id ) WHERE ISNULL( t.task_state, N'' ) <> '' AND ISNULL( DB_NAME( r.database_id ), N'' ) = 'MyDB' AND ISNULL( w.wait_duration_ms, 0 ) > CASE WHEN FORMAT( GETDATE(), 'HH:mm' ) >= FORMAT( CAST('07:00AM' AS TIME), N'hh\.mm' ) AND FORMAT( GETDATE(), 'HH:mm' ) < FORMAT( CAST('05:30PM' AS TIME), N'hh\.mm' ) THEN 10000 ELSE 20000 END --- this will change the wait time to 20s during offpeak hours. AND s.session_id <> ISNULL( w.blocking_session_id, 0 ) AND is_user_process = 1; ---- added on 2019-04-02 @8.40AM to neglect the parallel queries.threads blocking themselves IF ( @Waiting > 30 ) --send email