SQL Server Deadlock Analysis: Detect and Resolve Deadlocks

What is SQL Server Deadlock Analysis?

SQL Server Deadlock Analysis is a capability of SentryOne database monitoring products that helps DBAs find and fix SQL Server deadlocks, which are situations in which two or more tasks block each other by having a "lock" on a resource needed complete the task. By using SentryOne SQL Sentry, you can quickly resolve deadlocks and improve SQL Server performance.

You'll find this feature in:

SQL Sentry | Download Free Trial

Plan Explorer | Download Free Trial

Are SQL Server deadlocks bringing down your database performance?

Resolving SQL Server deadlocks can be a frustrating, time-consuming process, especially if you're dealing with multiple complex deadlock scenarios. A typical deadlock situation unfolds in this way: One stored procedure has taken a lock on resource A (such as an object, page, or row) and is waiting on the other procedure before it can access resource B. But that procedure first took a lock on B and is now waiting on the first procedure to release its lock on A. 

To break this downward spiraling cycle, you need to quickly identify and address the source of the deadlock. SQL Sentry, the flagship SentryOne monitoring product, gives you powerful tools for breaking through deadlocks and restoring peak performance.

Save time troubleshooting deadlocks

The Deadlocks tab in SQL Sentry displays a complete view of deadlocks in your SQL Server database—including the processes and resources involved—with visual representations that accelerate the deadlock troubleshooting process. 

With SQL Sentry Deadlock Analysis capabilities, you can:

  • View statements that executed while the deadlock was being detected
  • Avoid installing agents or set trace flags to gather the relevant information about deadlocks
  • Eliminate the need to translate SPIDs or reference IDsthe host, application, and resource names are labeled in the deadlock graph
  • Use Plan Explorer, which is integrated with SQL Sentry, to view deadlock files
See SQL Sentry Deadlock Analysis in action in this quick tour

Explore powerful SQL Server deadlock analysis capabilities

SQL Sentry gives you all the information you need to identify, analyze, and resolve SQL Server deadlocks. With the right tools, you can save time and resources in breaking through deadlock problems and optimizing SQL Server performance.

Speed the analysis process with powerful visualization

In SQL Sentry and Plan Explorer, deadlock diagrams are simple and easy to read. The diagram is laid in an intuitive pattern (circular, by default), and you can easily view the relevant reference IDs with clear labeling. 

  • Process and resources nodes are independently represented, along with any relationships between them
  • Deadlock victims are highlighted with a red background
  • Numbers and arrows indicate events that led to the deadlock
  • Letters indicate the requested lock mode

You can select object nodes in the graphical representation to focus on that object in the gridview. 

SentryOne_Deadlock_Graph_184

 

The Deadlock Graph in SQL Sentry displays deadlocks in an intuitive way to speed the troubleshooting process.

SentryOne-deadlocks-playback-functionality-184

 

Deadlock playback functionality in SQL Sentry helps you analyze the events that led to the deadlock.

Use deadlock playback for faster analysis

The deadlock playback capability helps you analyze the source of deadlocks by displaying the events that led to the deadlock. This functionality can help you reverse-engineer a deadlock.

With the playback, you can watch the diagram cycle through the events, which is like watching the deadlock happen again in real time. 

Playback controls include the ability to skip forward, fast forward, rewind, change the playback speed, and zoom in or out on the deadlock graph. 

 

Speed deadlock analysis with relevant, detailed metrics 

Deadlock Analysis capabilities in SQL Sentry provide detailed metrics so you can resolve deadlocks, cutting the time you spend restoring your database to peak performance.

Deadlock metrics collected

  • SQL Server where the deadlock took place
  • Time the deadlock occurred
  • Victim SPID—the session process ID of the victim involved in the deadlock
  • Victim host—workstation belonging to the victim thread
  • Victim application—application name belonging to the victim thread
  • Victim database
  • Victim text data
  • Deadlock XML—captured deadlock XML

 

Deadlock Analysis in SQL Sentry gives you the details you need to resolve SQL Server deadlocks.

Lock details

The lock details area breaks down the deadlock by specific lock types, including owners and waiters involved in each lock.

  • SPID—session process ID of the associated owner/waiter
  • Plan—opens a Plan Explorer session to view the associated query plan
  • Host
  • Application
  • Database
  • Log use—the amount of log space used by the process
  • Deadlock priority—zero (0) or normal is the default priority
  • Wait time—time in milliseconds spent waiting on the resource
  • Transaction start time
  • Last batch start time
  • Last batch completion time
  • Mode/type of resource lock
  • Status of the task
  • Current transaction isolation level
  • Login name of the session

What You Can Do with Deadlock Analysis 

  • Use an intuitive visualization of SQL Server deadlocks to analyze and resolve problems
  • Use deadlock playback to better understand the events that led to the deadlock
  • Customize deadlock metrics collection
  • Set custom alerts for deadlock occurrences
"SQL Sentry provides detailed graphical analysis on deadlock chains and involving parties. It is very intuitive and easy to pinpoint the cause of deadlocks."

—SentryOne Customer