In large environments with large OLTP databases, deadlocks are a common issue that can disrupt business continuity and cause user interruptions. While trace flags and Profiler can help identify deadlocks, Extended Events provide a lightweight and customizable way to monitor and capture deadlock events and related information. Extended Events are also meant to replace SQL Server Profiler, which is deprecated. This article will guide you on how to capture deadlock information using Extended Events in SQL Server.

Creating SQL Server Extended Events to Capture Deadlocks using Management Studio

To capture deadlock information using Extended Events in SQL Server Management Studio (SSMS), follow these steps:

1. Open SSMS and navigate to Management > Extended Events > Sessions.
2. Right-click on Sessions and select New Session.
3. In the new window, enter a name for your event (e.g., Deadlock_Capture).
4. Select the event to start at server startup and after session creation.
5. Go to the Events tab and type “deadlock” in the event library textbox to filter deadlock events.
6. Select the Lock_Deadlock and Lock_deadlock_chain events.
7. Click on the configure button and select the sql_text field to capture the query that caused the deadlock.
8. In the Data Storage tab, select where you want to store the Extended Event data (e.g., a file).
9. Click OK to save the Event configuration.

Creating SQL Server Extended Events to Capture Deadlocks using T-SQL

You can also achieve the same results as in SSMS by executing the following T-SQL code:

CREATE EVENT SESSION [deadlock_capture] ON SERVER 

ADD EVENT sqlserver.lock_deadlock(
    ACTION(sqlserver.sql_text)),
ADD EVENT sqlserver.lock_deadlock_chain(
    ACTION(sqlserver.sql_text))

ADD TARGET package0.event_file(SET filename=N'deadlock_capture')

WITH (MAX_MEMORY=4096 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=ON)

Testing SQL Server Extended Events with a Deadlock

To test the Extended Event for capturing deadlocks, you can create a deadlock scenario using two tables and two sessions. For example:

In session 1, run the following query:

BEGIN TRAN

UPDATE tableA
SET [value] = 'C1'
WHERE id = 1

WAITFOR DELAY '00:00:05'

UPDATE tableB
SET [value] = 'C2'
WHERE id = 1

In session 2, run the following query:

BEGIN TRAN

UPDATE tableB
SET [value] = 'C2'
WHERE id = 1

WAITFOR DELAY '00:00:05'

UPDATE tableA
SET [value] = 'C1'
WHERE id = 1

One of the sessions will throw a deadlock message. You can then review the captured data to analyze the deadlock.

Reviewing Data Captured by SQL Server Extended Events

To review the captured deadlock data using SSMS, navigate to Management > Extended Events > Sessions and expand the event you want to check (e.g., deadlock_capture). Double click on the target to view the captured data.

The lock_deadlock_chain events provide information about both sessions involved in the deadlock. The lock_deadlock event provides information about the deadlock victim and the T-SQL code involved. You can correlate the deadlock victim to the session_id using the transaction_id field.

Capturing More Information with XML Deadlock Report

In cases where troubleshooting deadlocks becomes more complex due to the queries’ complexity or involvement of more than two sessions, you can use the xml_deadlock_report option. This option allows you to visually analyze the deadlock and gain a better understanding of the issue.

To configure the xml_deadlock_report option, follow the same steps as before in SSMS, but select the xml_deadlock_report option. You can add additional fields or filters if needed.

Troubleshooting Extended Event Data Capture

If you are not seeing any data after testing, make sure the event is started. If it is not started, you can start it by right-clicking on it and choosing Start Session. You can also start and stop Extended Events programmatically using T-SQL code and SQL Agent jobs.

By capturing and analyzing deadlocks using Extended Events in SQL Server, you can effectively troubleshoot and resolve deadlock issues in your database server.

Article Last Updated: 2022-08-03