Monitoring Deadlocks In The SQL Server

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 rollback 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.

1. In the Event File Viewer, right-click and set the filter to display the xml_deadlock_report.

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, you can use the NOLOCK hint.

More information

• Microsoft – Analyze Deadlocks with SQL Server Profiler
• Microsoft – Monitoring SQL Database Deadlocks
• What are SQL Server deadlocks and how to monitor them, Jefferson Elias – SQLShack
• SQL Server Profiler Graphical Deadlock Chain, Jeremy Kadlec – MSSQLTips
• Finding SQL Server Deadlocks Using Trace Flag 1222, Ken Simmons – MSSQLTips