Share via

How to use Extended Events to proactively monitor your SQL Server for Deadlock issues

  • Article

Usually after a deadlock occurs, we don't have the needed information to troubleshoot the deadlock issue. This is because in order to troubleshoot a deadlock issue we need verbose information, so we need to have certain trace flags enabled (e.g. 1204, 1222). This is usually a problem because after setting up these trace flags, you still have to wait days, weeks or months for the deadlock issue to reoccur.

But there is a SQL Server feature you can use to capture verbose deadlock information at the first occurrence of the issue: Extended Events. You can configure an extended event in a SQL Server instance, which will record deadlock graphs inside a file as soon as the deadlocks occur. The overhead of this functionality is negligible and it serves as a “black box” for any deadlocks that occur. This means that you will not need to enable Trace Flags or capture Profiler traces to troubleshoot deadlock issues. The deadlock graph will be available for you at the first occurrence of the deadlock issue!

Here is how you can set it up:

-- Create a new event session (it is better to create a new session and not modify the system’s built-in session “system_health”):
CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.asynchronous_file_target
(SET filename= N'C:\temp\deadlock.xel' )
WITH
(MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=10 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

-- Enable the session to start capturing events:
ALTER EVENT SESSION [Deadlock_Monitor] ON SERVER STATE = start;

-- To see how many deadlocks have been captured by the session since it started running, you can run this query:
select COUNT(*) from sys.fn_xe_file_target_read_file ('c:\temp\deadlock*.xel', 'c:\temp\deadlock*.xem', null, null)

-- To get a list of the captured deadlocks and their graphs you can execute this query:
select xml_data.value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') Execution_Time,
xml_data.value('(event/data/value)[1]','varchar(max)') Query
from (select object_name as event, CONVERT(xml, event_data) as xml_data
from sys.fn_xe_file_target_read_file
('c:\temp\deadlock*.xel', 'c:\temp\deadlock*.xem', null, null)) v order by Execution_Time

-- If you want the session to stop capturing events (until you enable the session again), you can use this query:
ALTER EVENT SESSION [Deadlock_Monitor] ON SERVER STATE = stop;

-- If you want to completely remove (delete) the session from the server, you can use this query:
DROP EVENT SESSION [Deadlock_Monitor] ON SERVER

-- If you want to configure other events too, you can query these tables to find out what you can trace and how:

--Show the possible targets:
SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
AND xo.[object_type] = 'target'
ORDER BY xp.[name];

--Show the possible actions
SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
AND xo.[object_type] = 'action'
ORDER BY xp.[name], xo.[name];

Try it yourself! It will prove to be very handy for troubleshooting deadlock issues at their first occurrence!

Comments