Different ways to Monitor Deadlocks

Every database environment, deadlocks are common problems and it is important for DBA’s to monitor deadlocks and make sure to prevent them from reoccurring.

There are multiple ways to monitor deadlocks

Using Trace Flags 

We can enable two trace flags 1204 and 1222 to capture deadlock information in error log. Run the below code, this will enable deadlock trace flags.

DBCC TRACEON (1204,1222);GO

If you would like to enable deadlock trace flags globally, you can use the below

DBCC TRACEON (1204,1222,-1);GO


Let’s force deadlock by creating two tables. Please run these queries in the order

--Session 1:
CREATE TABLE tbl_dlck1 (colum1 INT);GO
UPDATE tbl_dlck1 SET colum1 = 2;GO
-- Session 2 :
CREATE TABLE tbl_dlck2 (colum1 INT);
UPDATE tbl_dlck2 SET colum1 = 2;GO
--Session 1:
UPDATE tbl_dlck2 SET colum1 = 2;
--Session 2:
UPDATE tbl_dlck1 SET colum1 = 2;


Let’s see the error log. Here’s the deadlock information in the error log.


Using Profiler: 

I have selected “Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events in the profiler. Here is the result


Using Extended Events:

There are two ways we can capture deadlock information one is with system_health and another one is to create own extended event session. System_Health is default extended event which runs continuously by SQL Server.

Using System_Health extended event. Use the below queries to get the information.

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 name = 'system_health' 

Using XPath we can then get just the deadlock graphs from the XML by using a derived table and a CROSS APPLY to get the individual Event Nodes as follows:

select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph
(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 name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

This query will take long time to execute, but there is no server performance impact but you would expect this query to take more time.


Using creating own extended session to capture the deadlock. Select xml_deadlock_report event as shown below



That’s all about deadlocks.





Hope you like the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter



About Ramasankar

My name is Ramasankar Molleti, I have been working on various database and cloud systems (SQL Server, Oracle, PostgreSQL, DynamoDB, Redshift, Amazon, Azure) over 12 years, primarily focusing on Architect , Development, Administration and Business intelligence. I hold various Certifications such as Amazon Web services Solution Architect Associate, Microsoft certifications (Microsoft Certified Information Technology Professional, Microsoft Certified Technology Specialist, and Microsoft Certified Solution Associate).
This entry was posted in Database Administration, Deadlock, Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s