Finding Deadlocks in SQL Server

Deadlocks occur in SQL Server when two or more processes are waiting for the same resource, and each process is waiting for the other process to complete before moving forward.

How do I know if deadlocks are happening to me?

You may notice your application being quite sluggish, or some of your users may be complaining about error messages and slowness. You may also see a message in your SQL Server log along the lines of “Transaction (Process ID 103) was deadlocked on resources with another process and has been chosen as the deadlock victim.”

How can I capture more information about them?

There are a couple of things you can try to help diagnose this issue, such as enabling trace flags to write detailed information to the SQL Server logs. You can even use a SQL Server Profiler to run a trace, which will figure out where those pesky deadlocks are happening. However, it is better to use Extended Events (the monitoring system for newer SQL Server versions) instead of the SQL Server Profiler, as it is much less resource intensive and can usually be run on live production environments without causing too many issues.

Using trace flags to capture to the log

There are two main trace flags that can capture any deadlocks happening in your database. Namely, these are the flags 1204 and 1222, and both can also be used at the same time. Knowing what each flag does is helpful when trying to diagnose issues. Flag 1204 will list the information by node, while flag 1222 will list by resource and process.

It’s very simple to enable these trace flags on SQL Server. You would run the below two commands in your SQL Server Management Studio (SSMS):

DBCC TRACEON(1204, -1)

DBCC TRACEON(1222, -1)

The -1 in these commands means that they will be enabled globally on your server, rather than just for whichever query that you may be running through SSMS.

Turning them off is also just as easy:

DBCC TRACEOFF(1204, -1)

DBCC TRACEOFF(1222, -1)

To check if the flags are enabled you can run this:

DBCC TRACESTATUS(1204, -1)

DBCC TRACESTATUS(1222, -1)

The image below represents what you might see in the SQL Server logs when you have enabled trace flags:

trace

You most likely would not want to read through everything these commands return manually. Instead, it is much better to represent the information graphically.

Capturing a deadlock graph with SQL Server Profiler or Extended Events

Capturing deadlock information graphically largely depends on which version of SQL Server you are running. For earlier versions such as 2005, 2008 and 2008R2, SQL Server Profiler will be your best bet as Extended Events weren’t introduced in these versions. As for the newer versions of SQL Server such as 2012, 2014 and 2016, Extended Events should be your preferred choice. The image below demonstrates what the graph may look like:

graph

Extended Events

Let’s focus on the newer technology. Going forward, SQL Server Profiler will be removed from SQL Server, and Extended Events will be taking the torch. You have two options for getting information about deadlocks from Extended Events. The first is system_health which runs continuously by default. You can use this to mine data for deadlocks which have happened in the past, or you can alternatively set things up to only capture information related to deadlocks.

Ideally, you should file things on xml_deadlock_report, which should give you the most pertinent information. There is also a way of using T-SQL to query, such as using the following query to generate some XML:

SELECT DeadlockEventXML.query(‘(event/data[@name=”xml_report”]/value/deadlock)[1]’) AS deadlock_graph

This should generate some XML which you can open in SSMS in order to view the graph, after saving it as a .xdl file.

 

You may have noticed that I haven’t shown how to run a Server Side Trace, and this is because they will be removed from future versions of SQL Server, so they should be used as little as possible going forward. Hopefully this brief introduction into capturing deadlocks will help you identify any that may be happening to you.