How to monitor deadlock using extended events in SQL Server 2008 and later

Before I talk about how to monitor the deadlocks let us have a small brief what is deadlock and how we were handling them in the previous versions of SQL Server.

 

Deadlock:

A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server.

 

How SQL Server resolves a deadlock?

In SQL Server Deadlock can be detected and resolved automatically without manual intervention. Here in SQL Server, Deadlock Monitor (Basically a Lock Monitor) scans through the Processes or threads every 5 seconds and if any such scenario exits, it terminates the query batch involved in that processes based upon cost of resources used.

Mentioned below are trace flags which are classical way to enable Deadlock trace in SQL Server Editions.

1. Enable Trace Flag 1204 - Here data are captured in node level.

Dbcc traceon (T1204,-1)

2. Enable Trace Flag 1222 - Here data is returned in XML Format. Also one can see the processes information also which are involved in deadlock.

DBCC Traceon (T1222,-1)

3. Run SQL Profiler Trace to capture the Deadlock events.

This deadlock information can be viewed in SQL Server Error Logs and Trace Files.

The other solution to the keep continuous monitoring and capturing the deadlock information is to use extended events.

Extended Events:

SQL Server extended events are the light weight event driven (fired) data collection for SQL Servers. It is much beneficial in case of tracing in the busy servers. It’s fundamentally an advanced version of Profiler Trace.

This article shows how to configure and monitor deadlock graph using extended events.

Till SQL Server 2008 R2 the X-events must be created using scripts. From SQL Server 2012 we can configure extended events using GUI.

By default the system health event will log the deadlock information for the instance. We can use the below sample query to list out deadlock events from the system health.

Use Master

SELECT

       xed.value('@timestamp', 'datetime') as Creation_Date,

       xed.query('.') AS Extend_Event

FROM

(

       SELECT CAST([target_data] AS XML) AS Target_Data

       FROM sys.dm_xe_session_targets AS xt

       INNER JOIN sys.dm_xe_sessions AS xs

       ON xs.address = xt.event_session_address

       WHERE xs.name = N'system_health'

       AND xt.target_name = N'ring_buffer'

) AS XML_Data

CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)

ORDER BY Creation_Date DESC

 

If we want to monitor the deadlocks explicitly, we can create a dedicated session for deadlock events. In this blog I will walk you through the steps to create sessions to monitor deadlock events.

 

Enabling Deadlock information using UI:

 

1. Connect to SSMS and connect to the SQL Server 2012 instance. 

 

 

 

2. There are two default sessions in extended events as shown in the above picture. Create new session wizard

 

 

        

 

            

 

3. Type the session name. We can start the event session at server startup by checking the check box. 

            

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4. There are free defined templates for locks and blocking, system health and query execution

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

         

  

 

5. We can select a template based on the requirement however for deadlock graph we will add the events manually.

     

 

 

   

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6. Add xml_deadlock_report event as shown in the below figure.

      

 

     

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

    

 

 

 

 

7. select the global fields which are needed

      

 

 

8. Save the output either to file or ring buffers.

 

                     

 

 

9. We can minimize the number of events in the ring buffers

                                 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

10. Here is the summary of all the inputs for the event selection

 

 

11. We have created the session successfully. We can start the session immediately or we can run it on demand

 

                  

 

12. You should be seeing now that an event which was configured is now in stopped state

 

 

 

Creating a deadlock Scenario :

 

Analyze the event data : event file lists all the deadlock reports with the timestamp. We can look for the specific timestamp and analyze the XML graph.

 

 Graphical representation of deadlock:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

We can click on xml report to see the xml report of the deadlock graph. 

 

 

These are some of steps to configure the extended events for monitoring Deadlock.

I would like to discuss about two more events which are available in X-events that can help us to drill down the deadlock issues further.

1. Lock: Deadlock Event Class

   This class can be used to identify the deadlock victim. This event rises whenever the request to acquire a lock is cancelled as this has been chosen as a deadlock victim.

  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. Lock: Deadlock chain

This event class is also used to monitor the deadlock scenarios. This event will be raised whenever there is a deadlock situation. By monitoring this events at the instance level, we can identify which objects are involved in deadlock and do we have any performances issues in the application due to deadlocks.

 

 

 

Using these two event classes two monitor deadlocks in the SQL server :

 

 

 

 

 

 

 

 

 

 

 

 

 

We can watch the live data from these units by checking the check box in the above screenshot.

 

Below example shows a simple deadlock scenario:

 

 

 

Looking at the extended events live data

 

 

 

 

 

If any users comes with issue saying “There was a deadlock reported in one of Application error log” which has occurred midnight now you know where we can find the data.  

 

Author : Sravani Saluru, Support Engineer, Microsoft India GTSC

Reviewed by:

Karthick PK, Technical Lead, Microsoft India GTSC

Amit Khandelwal, Technical Lead, Microsoft India GTSC

Comments

• Anonymous
  April 27, 2013
  Since the xml_deadlock_report is a part of the system_health session and has been since 2008, why would you need to create another session to do the same thing?  Even on really busy systems the event definition of system_health minimizes what events are being collected and would allow the deadlock information to still exist in the ring_buffer typically, and if not there, definitely in the event_file data for the event session.  

• Anonymous
  May 02, 2013
  Yes, I have also mentioned about the default system health event in the blog. However if we want to monitor deadlocks specifically and have a customized targets we can use the above.

• Anonymous
  June 05, 2013
  Will the dbcc traceon command work with the T before the trace flag number?

1.       Enable Trace Flag 1204 - Here data are captured in node level. Dbcc traceon (T1204,-1)
2.   Enable Trace Flag 1222 - Here data is returned in XML Format. Also one can see the processes information also which are involved in deadlock. DBCC Traceon (T1222,-1

• Anonymous
  June 07, 2013
  Exhaustive article ...!!!

• Anonymous
  January 20, 2014
  Very useful article :) Clearly described.

• Anonymous
  May 08, 2014
  Do you have the sql scripts to create the extended events to monitor deadlocks for sql server 2008 R2?

• Anonymous
  June 19, 2014
  Thanks for the script

• Anonymous
  July 08, 2014
  I am unable to see the Extended Events drop down. I am using SQL Server 2008 R2. Please let me know how to add it/install it.

• Anonymous
  May 24, 2015
  Very useful article. Thanks for sharing this!!