Deadlock Analysis for SQL Server - SentryOne: Monitoring Platform

What is SQL Server Deadlock Analysis?

SQL Server Deadlock Analysis is a capability in SQL Sentry 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 IDs—the 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.

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

More about Deadlock Analysis

More resources

"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

Ready to end your database performance problems?

Download a SQL Sentry trial

See for yourself how SQL Sentry can help you achieve optimal database performance.

Schedule a SQL Sentry demo

Learn more about how SQL Sentry can help you troubleshoot and prevent database performance problems. Register to attend a public demo or schedule a one-on-one session.

Learn more about SQL Sentry

Discover how you can quickly find and fix SQL Server performance problems with SQL Sentry, which delivers proven scalability, easy-access dashboards, and visibility across your entire database environment.