Finding Deadlocks in SQL Server

QUICK RECAP - WHAT IS A DEADLOCK?

Deadlocks occur in SQL Server when two (or more) processes are waiting to acquire a resource (such as a lock) that is currently held be the other. Neither can continue because it is blocked by the other process, so you get a deadlock. The diagram below illustrates this:

Finding Deadlocks in SQL Server

In this situation, one of the processes will be chosen as the deadlock victim and rolled-back. An error may be logged to the SQL Server Event Log and the client application will need to re-attempt the transaction.

HOW DOES SQL SERVER CHOOSE A DEADLOCK VICTIM?

This is pretty well documented so let’s not reinvent the wheel. Head over to SQLShack.com and view this article.

HOW DO I KNOW IF DEADLOCKS ARE HAPPENING ON MY SERVER?

The most obvious way of knowing that deadlocks are occurring is that your users may be complaining about error messages within client applications (although depending on how the application handles errors, this may not be particularly obvious). It’s also possible that users are reporting sluggish performance (for example, if the application’s retries the transaction after the error).

However, the clearest way is that you’ll see messages in your SQL Server Event log along the lines of 

“Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”

HOW CAN I LOG MORE INFORMATION ABOUT DEADLOCKS?

There are two trace flags that you can enable to capture more details on deadlocks These are 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.

To enable these flags globally, execute the follow commands:

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

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:

CAPTURING DEADLOCKS INFORMATION WITH PROFILER OR EXTENDED EVENTS

SQL SERVER PROFILER

Detecting deadlock events with SQL Server Profiler is fairly straightforward and well documented. This would be my choice if I knew the specific Window when deadlocks are occurring. However, for capturing data over an extended period of time, Extended Events is a better choice.

EXTENDED EVENTS

You have two options for getting information about deadlocks from Extended Events. The first is the built-in system_health session (enabled by default). This session captures more than just deadlocks however, so you’ll need to query the captured data to extract only the deadlocks.

To focus only on deadlocks and the deadlock chain, you’ll need to create a specific event session that focusses only on the required events. This article by Eduardo Pivaral over on mssqltips.com gives a good example of how to create an Extended Events Deadlock session using both the SSMS GUI, and script. It also explains how to similate a deadlock and then extract the information captured via the GUI (just be sure to start your session before simulating the deadlock).

To extract the information by script, you need to query the event session data. This method for doing this varies depending on how you’ve configured the event, and there are tonnes of examples out there. Here’s a simple example based on the example in the link above:

SELECT  xe.event_data
FROM    sys.fn_xe_file_target_read_file(N'deadlock_capture*.xel', N'deadlock_capture*.xem', null, null)
        CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xe
 

Thanks for reading!

Find out more about our services, products or read more about us.