SQL Server Performance Tuning

Monitoring Deadlocks In The SQL Server

Mark Varnas
No comments

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

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials