Alerting Mechanism from Extended Events for Deadlock & Blocking

Hi Mates,

Today I got a question from one of my friends saying that is there we can setup an alerting mechanism when an event gets captured in Extended events.

It  sounded very interesting to me & thought of exploring it.

NOTE: The examples whatever I shared is just for testing & in fact I feel this is not really productive. You can make use of this as just an exploration step.

The main problem with extended events is that even though the events would get captured there is no way we can trigger an alert from it. Unlike Notification services where we can get instant alerts. This is why people are still relying on them.

Below are the links which provides an alternative solutions to overcome this however one requires knowledge of C# programming language & for the other we need to download free 3rd party tool.

Extended events through T SQL collector

C # related monitoring

In the below examples we are going to achieve the same but by making use of SQL Server in built alerts.

Example 1: Getting an Alert for deadlock Instantaneously from extended events.

Step 1: Here I am creating the stored procedure for which would you need to pass just the profile name & your mail id. Schedule the below code as job with out a schedule.

create proc dba_ProcessDeadlockGraphs
as
set nocount on;
DECLARE @deadlock XML;
DECLARE @email_message nvarchar(MAX);

select top 1
    @deadlock=DeadlockGraph
from (
SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
, XEvent.value('(/event/@timestamp)[1]','DATETIMEOFFSET') AS BeginTime
    FROM ( SELECT XEvent.query('.') AS XEvent
        FROM ( SELECT CAST(target_data AS XML) AS TargetData
            FROM sys.dm_xe_session_targets st
                JOIN sys.dm_xe_sessions s
                ON s.address = st.event_session_address
            WHERE s.name = 'system_health'
                AND st.target_name = 'ring_buffer'
              ) AS Data
              CROSS APPLY
                 TargetData.nodes
                    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
              AS XEventData ( XEvent )
      ) AS src
) result
order by BeginTime desc

SELECT @email_message = CONVERT(nvarchar(max), @deadlock)
EXEC msdb.dbo.sp_send_dbmail
             @profile_name = 'xxxxx', -- your defined email profile
             @recipients = 'xxxxxx', -- your email
             @subject = 'Deadlock Notification',
             @body = @email_message;

Step 2: create the below alert inside alerts under SQL Server Agent.

USE [msdb]
GO

/****** Object:  Alert [Deadlock Alert]    Script Date: 12/22/2018 8:50:14 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N'Deadlock Alert',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition=N'Locks|Number of Deadlocks/sec|_Total|>|0',
@job_id=N'a41a2fcd-6171-4335-9526-226312913b64'
GO

Step 3: As expected nothing else needs to be done. you will get an alert like what you see in the picture below.

Example 2: Getting an Alert for Blocking Instantaneously from extended events.

Unlike Deadlocks which can be captured from System health sessions. We need to have dedicated session to capture Blocking from Extended events. Also Ensure that you have enabled
Blocking process Threshold under sp_configure according to your needs. Say 1 minute would be ideal as anything less than that would bloat our mail box  if we are operating on busy system.

Step 1: As I said you can configure Blocking session from GUI when you are running from SQL 2012 however if you are on SQL 2008 or 2008 R2 you need to make use of script.

Kindly change your target file destination path as per your drive layout.

CREATE EVENT SESSION [Capture_BlockedProcessReport]

ON SERVER

ADD EVENT sqlserver.blocked_process_report

ADD TARGET package0.event_file(

SET filename=N'C:\Temp\Capture_BlockedProcessReport.xel'

)

WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,

TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

GO

 

/*

start the event session

*/

ALTER EVENT SESSION [Capture_BlockedProcessReport]

ON SERVER

STATE = START;

GO

Step 2: Just like in deadlocks where we created an Stored procedure I did the same for Blocking as well.

create proc dba_blockingmonitor as
set nocount on;
DECLARE @blockingreport XML;
DECLARE @email_message nvarchar(MAX);

select top 1
    @blockingreport=[blocked_process_report]
from (
SELECT
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
DATEADD(mi,DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [event time],
    event_data.query('(event/data[@name="blocked_process"]/value/blocked-process-report)[1]') as [blocked_process_report]
FROM
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\Temp\Capture_BlockedProcessReport*.xel', NULL, NULL, NULL)
) AS sub
) result order by [event time] desc

SELECT @email_message = CONVERT(nvarchar(max), @blockingreport)
EXEC msdb.dbo.sp_send_dbmail
             @profile_name = 'xxxxxx', -- your defined email profile
             @recipients = 'xxxxx', -- your email
             @subject = 'Blocking Notification',
             @body = @email_message;

Step 3: create the SP as a job & like earlier no need to schedule it as we want the alerts to be generated instantaneously.

Step 4: As like Deadlocks we need to create an alert so that our blocking report would get triggered
to our mailbox up on generation of  Blocking on the server.

USE [msdb]
GO

/****** Object:  Alert [block]    Script Date: 12/22/2018 9:10:28 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N'block',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@performance_condition=N'General Statistics|Processes blocked||>|0',
@job_id=N'b529ddac-5933-4d07-aecf-3caa9122c8ab'
GO

NOTE: I have not given importance to generate in proper html format as this is just for testing.
Also I strongly believe that in your environments you would be definitely having better monitoring mechanism than this. This is just my thoughts on implementing alerting from extended events just like what we have for Event notifications.

Please feel free to share your comments & also correct me if I am wrong anywhere.

Happy Reading.