Have you ever encountered a deadlock error in SQL Server? If so, you’re not alone. Deadlocks can be a common issue when working with SQL Server, and knowing how to handle them is crucial for database administrators.

Traditionally, DBAs would enable trace flag 1222 to capture deadlock information in the SQL Server ERRORLOG file. However, there is a more efficient way to capture this information using the System_Health session.

The System_Health session is a built-in session in SQL Server that captures a lot of extended events by default, including deadlock information. Here is the script to create the System_Health session:

CREATE EVENT SESSION [system_health] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

By default, SQL Server captures the XML deadlock graph using this session. To test this, you can create a database and two tables, and then reproduce a deadlock error. Here is an example:

CREATE DATABASE DeadlockDemo
GO
USE DeadlockDemo
GO
CREATE TABLE MyT1 (i INT)
GO
CREATE TABLE MyT2 (i INT)
GO
INSERT INTO MyT1
VALUES (1)
GO
INSERT INTO MyT2
VALUES (1)
GO

To reproduce the deadlock error, you need two connections. Here are the steps:

1. Connection #1: Begin a transaction
2. Connection #2: Begin a transaction
3. Connection #1: Update MyT1 set i=3
4. Connection #2: Update MyT2 set i=3
5. Connection #1: Select * from MyT2
6. Connection #2: Select * from MyT1

After performing these steps, one of the connections will encounter a deadlock error. You can then view the deadlock graph captured by the System_Health session using the following query:

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM (
	SELECT XEvent.query('.') AS XEvent
	FROM (
		SELECT CAST(target_data AS XML) AS TargetData
		FROM sys.dm_xe_session_targets st
		INNER 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 source;

By using the System_Health session, you can easily capture and analyze deadlock information without the need for trace flags or manual intervention. This can greatly simplify the troubleshooting process for DBAs.

Next time you encounter a deadlock error in SQL Server, remember to leverage the System_Health session to capture and analyze the deadlock graph. It’s a powerful tool that can help you identify and resolve deadlock issues more efficiently.

Have you ever experienced a deadlock in SQL Server? Share your experience in the comments below!