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 on SQL Server, it decides to abort one of the processes that cause a “victim”, allowing 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.
By the way, this check is a part of our SQL Server Health Check service.
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 traceflag 1222 (or 1204 on SQL 2000) to capture deadlocks into 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 following the steps:
- Open SSMS, expand “Managment.
- Clock on Extend events, and expand Sessions.
- Click on system_healt, and Double click on package0_event_file to open the extended event file.
- In the event file viewer, right-click and set the filter to display the xml_deadlock_report.
- It shows the deadlocks 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- Reducing the lock time – do performance tuning in the objects involved. (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.
