Should we really need to setup monitoring to capture deadlocks in SQL Server ???

I’ll like to begin this blog  with short and usual conversation in DBA space !

———————-start conversation——————–

App team: Hi

DBA Team: Hello

App Team: we observe the following error in our application logs –“Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction”. Can you please provide the cause of the error?

DBA Team : Those errors are deadlock errors and We are sorry to say that deadlock information would be captured in SQL server only when we set up monitoring in place i.e. using Trace flags or enable SQL Sever profiler/ server side trace.

———————-end conversation——————–

I am not flabbergasted at the above conversation as most of us provide the same response in our own grammatical way. However, the above conversation holds good for Pre SQL 2008 era.

From SQL Server 2008 on-wards, we  can make use a new feature called System_Health Session which is based on Extended Events. It will be ‘On’ seamlessly and gets recreated every time SQL server restarts.

System_Health Session monitors various events and deadlock is one of the event which is captured whenever it occurs.


Below is small demo on how to gather data on SQL 2008 /SQL 20008 R2 and SQL 2012 on-wards. I used SQL 2008 R2 and SQL 2014 for this demo.


a) Using the random script on web simulated deadlock on a demo database called deadlock. The following error is received:


b) System_health session logs/writes a the data on a target called Ring Buffer (Bing for Ring Buffer to know internals of ring buffer). Using the following query we can read the details captured in ring buffer in XML format

select CAST(xet.target_data as xml) from sys.dm_xe_session_targets xet
join sys.dm_xe_sessions xe
on (xe.address = xet.event_session_address)
where = ‘system_health

The XML contains the complete deadlock report similar to Trace flag 1205. The analysis of the XML file is outside the scope of this blog-post.




Microsoft made our lives easier by adding system_health session to GUI. It is very easier to access the system health session data i.e. scroll through Management folder–>Extended Events –> system_health.

The system health session contain two targets -event file and ring buffer. Ring buffer is similar to one discussed above. Event file contain captures a event called xml_deadlock_report which proides the deadlock information in graphical format as shown in below screenshot.




Conclusion: Extended events and system_health session is one of the interesting subjects to dwell into hence recommend to ‘Bing’ on this subject to learn more. Indeed it is better to substitute sql profiler with extended events to capture respective details without much performance overhead on the server. I would like to thank all the SQL server stalwarts who inspired me to share my knowledge through this blog.





Leave a comment

Filed under Deadlock, sql server 2008, sql server 2012

Leave a Reply

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

You are commenting using your 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