Extended Events and Deadlock Graphs

Published 03 November 10 08:10 PM | MartinBell

Extended events are a new feature in SQL Server 2008 available in the Enterprise Edition. When I demonstrate Extended events, two questions are usually asked.

1. Can I raise an alert when an extended event is raised?

2. Can I write to a Service Broker queue?

Unfortunately, at the moment the answer to both is no! The reasoning behind the questions lie the questioner’s desire to pro-active monitor SQL Server.

So without these two how useful are Extended Events? I think that they are still very useful. SQL Server Auditing is based on Extended Events so anyone wanting to use SQL Server Audit to monitor system or database configuration changes or audit DML statements on their systems you will be using them even if they don’t realise it.

Articles like
this from MVP Jonathan Kehayias shows you how you can extract deadlock graphs from the default System Health session. One thing to remember when using a ring buffers target for an extended event is that they have a limited size and to avoid becoming full they are maintained on a FIFO (first-in first-out) basis. In the case of the System Health session this is a limit of 4MB. As the System Health session monitors several events it is possible that your deadlock graphs are forced out of the ring buffer, therefore I would recommend that, if you want to be certain of obtaining the deadlock graph, then you create your own Extended Event session. This is very easy if you have the SQL Server 2008 Extended Events SSMS Addin from Codeplex installed on your system. With this you can script out the System Health session and modify it to your own requirements. In true Blue Peter fashion here is one I prepared earlier!!

CREATE EVENT SESSION [deadlock_capture]
ON SERVER
ADD
EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer(
     SET max_memory=4096)
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON)

ALTER EVENT SESSION [deadlock_capture] ON SERVER STATE = START

Unfortunately this doesn’t mean that all your problems have been solved, as a ring buffer is held in memory, when you stop the session the information collected will be lost, this also means that when you cycle SQL Server the information is lost. You will therefore need to persist the data in some way.

In my article on WMI Event alerts I used the database DBAEventManagement and a table DeadlockEvents to store deadlock graphs, so I will re-use the table to store deadlock graphs from the Extended Event.

You could write a SQL Agent job that checks the ring buffer for new data after a specific period. A full example script for such a job can be found here, but the step is as follows:

SET QUOTED_IDENTIFIER ON ;
DECLARE @startdate datetime2 ;

SET @startdate = ISNULL(( SELECT MAX(alerttime)  FROM dbo.DeadlockEvents ),'20000101') ;

WITH ring_buffer ( xmlcol )
AS (
       SELECT CONVERT(XML, st.target_data) as ring_buffer
       FROM sys.dm_xe_sessions s
       JOIN sys.dm_xe_session_targets st ON st.event_session_address = s.[address]
       WHERE s.name = 'deadlock_capture'
)
INSERT INTO dbo.DeadlockEvents ( AlertTime, DeadlockGraph )
SELECT CAST(T2.evntdata.value('(@timestamp)[1]','varchar(24)') AS datetime2) AS [AlertTime]
       ,CAST(T2.evntdata.value('(data[@name="xml_report"]/value)[1]','varchar(max)') AS XML) AS
deadlock_report
FROM ring_buffer
CROSS APPLY xmlcol.nodes('/RingBufferTarget/event[@timestamp > sql:variable("@startdate") and @name="xml_deadlock_report"]') as T2(evntdata) ;

You may notice that I don't do the changes for the mal-formed XML in the deadlock graph, if you are running SQL Server 2008 SP1 CU6 or higher this will not be necessary, also the RTM version of SQL Server 2008 R2 has the same problem which is fixed in CU1.

For anyone who's not looked at deadlock capture before will need to know that when you use the ring buffer target, rather than create new rows for each deadlock only one row is created and the XML within the data is updated for each subsequent deadlock graph. This may save a little space (153 bytes per deadlock graph!), but it does make it slightly more difficult to process individual deadlock graphs when persisting them in a table.  This is an example of a single deadlock and this is an example of two deadlocks. If you look at Jonathan’s solution you will see that using CROSS APPLY and the nodes function will allow you to retrieve each event as a separate row. For my SQL Agent job, I also do the event name check within XQuery for the nodes method and I also check the timestamp attribute for the event against the most recent timestamp of the deadlock graphs (AlertTime) stored, this makes sure I don’t insert duplicate rows.

Using a polling method may not be a suitable way to gather this information especially on a heavily loaded system. Although you can’t raise an alert directly from an Extended Event, it would be possible to use the Extended Event data in conjunction to a WMI Alert. As the deadlock graph can be retrieved from the EventData of the WMI Alert why use the Extended Event? One reason is, that to extract the deadlock graph, you have to enable token replacement in SQL Agent Alerts (see my previous article). This could have a security implications and retrieving the deadlock graph from an Extended Event will not require token replacement to be enabled.

To create the job to service the WMI alert use the script here. If you look at the job step you will see the following SQL:

SET QUOTED_IDENTIFIER ON ;

DECLARE @startdate datetime2, @time datetime, @rowcount int, @max_iterations int = 5 ;

SET @startdate = ISNULL(( SELECT MAX(alerttime)  FROM dbo.DeadlockEvents ),'20000101') ;

SELECT @rowcount = 0 , @time = DATEADD (ms,max_dispatch_latency,0)
FROM sys.server_event_sessions
WHERE name = 'deadlock_capture' ;

WHILE @rowcount = 0 AND @max_iterations > 0
BEGIN
       WITH ring_buffer ( xmlcol )
       AS (
              SELECT CONVERT(XML, st.target_data) as ring_buffer
              FROM sys.dm_xe_sessions s
              JOIN sys.dm_xe_session_targets st ON st.event_session_address = s.[address]
              WHERE s.name = 'deadlock_capture'
       )
       INSERT INTO dbo.DeadlockEvents ( AlertTime, DeadlockGraph )
       SELECT CAST(T2.evntdata.value('(@timestamp)[1]','varchar(24)') AS datetime2) AS [AlertTime]
              ,CAST(T2.evntdata.value('(data[@name="xml_report"]/value)[1]','varchar(max)') AS XML) AS deadlock_report
       FROM ring_buffer
       CROSS APPLY xmlcol.nodes('/RingBufferTarget/event[@timestamp > sql:variable("@startdate") and @name="xml_deadlock_report"]') as T2(evntdata) ;

       SET @rowcount = @@ROWCOUNT ;

       IF @rowcount = 0
              WAITFOR DELAY @time ;

       SET @max_iterations-=1 ;
END

The SQL for this step is more complex than the SQL used in the polling job. The additional logic is required because writing to the ring buffer is an asynchronous  task. Therefore when the WMI Alert fires the data may not be in the Extended Event’s target ring buffer. To cater for this, I do a check to see if a row has been inserted into the DeadlockEvents table. If it hasn’t I wait for a given period and retry. The delay is governed by the maximum dispatch latency configured for the Extended Event Session, if you want you can configure this to less than this maximum. I also have a backup counter that will mean that the loop will only iterate a maximum number of times.

To create the WMI alert you can use the following script that will run the Deadlock Capture job if an alert is raised:

USE [msdb]
GO

DECLARE
@job_id uniqueidentifier ;

SELECT @job_id = job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Deadlock Capture'

EXEC msdb.dbo.sp_add_alert @name=N'Deadlock Capture',
              @message_id=0,
              @severity=0,
              @enabled=1,
              @delay_between_responses=0,
              @include_event_description_in=0,
              @category_name=N'[Uncategorized]',
              @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
              @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
              @job_id=@job_id
GO

If you wanted to be emailed when a deadlock occurs you can add notifications to this alert.

I should also point out that saving the deadlock graph from the Extended Event as an .xdl file, will not allow SSMS to display it in a graphical format as the deadlock graphs from Extended Events use a different schema to those created using the SQL Trace event.

Comments

# Martin Bell UK SQL Server MVP said on November 6, 2010 05:48 PM:

If you have a single victim deadlock report from an Extended Event you may want to convert them into the format used by SQL Trace

This Blog

SQL Blogs

Syndication