SQL Server Performance Tuning

Monitoring Deadlocks in the SQL Server

Updated October 26, 20263 min read

Monitoring SQL Server deadlocks. Two delivery trucks sit nose to nose on a one-lane bridge, neither able to pass.

What is deadlock in SQL Server?

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

  • When this happens, SQL Server decides to abort one of the processes, making it the "victim" to allow another process to continue its transaction in the database.
  • The aborted transaction is rolled back, and an error message is sent to the user of the aborted process.
  • Generally, the transaction that requires the least amount of overhead to roll back is the transaction that is aborted.

Why should you care about deadlocks?

Deadlocks can put pressure on the resources of the SQL Server, especially on the CPU.

They can cause a big headache because deadlocks usually occur intermittently and can involve processes that run independently of each other.

How can I capture them?

You can enable trace flag 1222 (or 1204 on SQL 2000) to capture deadlocks in the error log.

On SQL Server 2008 or later, the system_health extended event session is enabled by default and automatically captures the deadlock graph.

You can check it by following these steps:

  1. Open SSMS, and expand Management.
  2. Click on Extended Events, and expand Sessions.
  3. Click on system_health, and double-click on package0_event_file to open the extended event file.
Figure 1 - Opening the extended event file
Figure 1 - Opening the extended event file
  1. In the Event File Viewer, right-click and set the filter to display the xml_deadlock_report.
Figure 2 - Opening the xml_deadlock_report
Figure 2 - Filter extended event information
  1. It shows the deadlocks that have occurred for the SQL instance.

How can you fix them?

Usually, a more in-depth analysis is needed to understand and solve them.

But you can start with:

  1. Reduce lock time by performing tuning on the involved objects (sometimes, an index can solve the problem).
  2. In the latter case, consider read committed snapshot isolation (RCSI) instead of NOLOCK; see SQL Server NOLOCK and isolation levels.

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜