Monitoring Deadlocks in SQL Server

Monitoring Deadlocks in SQL Server

What Is a Deadlock in SQL Server?

A deadlock occurs when there’s a circular dependency between two or more threads or processes over some resource within SQL Server.

When this happens in SQL Server, it resolves the situation by choosing one of the processes as the “victim” and cancelling it, allowing the other process to continue its work in the database.

The cancelled transaction is rolled back and an error message is sent to the user of the cancelled process.

In general, the transaction chosen as the victim is the one that requires the least overhead to roll back.

Why Should You Care About Deadlocks?

Deadlocks can put pressure on SQL Server’s resources, particularly the CPU.

They can be a real headache because deadlocks typically occur intermittently and can involve processes that otherwise run independently of one another.

How Can We Capture Them?

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

You can check it using the following steps:

1. Open SSMS and expand “Management”.
2. Expand Extended Events, then Sessions.
3. Click on system_health and double-click package0_event_file to open the extended events file.

1. In the event file viewer, right-click and set the filter to show xml_deadlock_report.

1. This shows the deadlocks that have occurred on the SQL instance.

How Can You Fix Them?

Usually, understanding and resolving deadlocks requires deeper analysis.

But you can start with the following:

1. Reduce the deadlock window — tune the performance of the related objects. (sometimes an indexing issue is the actual fix).
2. In some cases, you can use the NOLOCK hint.