Monitor Deadlocks

Applies to the following products and features: The on-premises SentryOne Portal feature for SQL Sentry. See the Getting Started with SentryOne Portal article for more details.

Deadlocks

The Deadlocks view provides details about deadlocks within your monitored environment. Use it to identify and fix deadlock issues on your monitored servers.

SentryOne Monitor Deadlocks List

Note:  Deadlocks for Amazon RDS are not supported in SentryOne Monitor or the on-premises SentryOne Portal.

Deadlock Diagram

The deadlock diagram is built from the captured deadlock XML. The victim, process, and resource (e.g. Object Lock) nodes are represented, as well as any relationships that exist between them.

Note: 

  • Resize the deadlock diagram using the magnifying and minimizing glass icons, and reset it to the original if needed. 
  • Select different nodes on the diagram to change the information displayed on the screen.
  • Use the expand button to open the deadlock diagram and XML in a full window.
  • Drag and drop deadlock files into the deadlock diagram space to get a diagram and view additional information.

SentryOne Monitor Deadlocks Diagram

The deadlock victim is highlighted in red (shown as Victim 56 [0] in the example above). The victim is selected, and the Node Details and Locks associated with the victim are displayed to the right in the image. There are two resource nodes (shown as Key Lock, this could also be at a different level such as an Object Lock or a Page Lock), and a process node (shown as Process 246 [0]). Select any of the nodes to display the associated Node Details and Locks (if applicable).

Note:  The numbers (1,2,3, and 4) and associated arrows that connect the nodes indicate the sequence of events that took place to create the deadlock.

Deadlock Details

ColumnDescription
SPID [ecid]The session process ID of the associated owner/waiter.
HostThe server or workstation name.
ApplicationThe associated application (e.g. a SQLAgent Job, .Net SqlClient Data Provider, name of a specific application running SQL statements against the associated database, etc.).
DatabaseThe associated database.
LoginThe user login associated with the session.
Log UsedThe amount of log space used by the process.
Deadlock PrioritySpecifies the Deadlock Priority.  Zero (0) or Normal is the default priority. In cases where each session has the same Deadlock Priority, SQL Server chooses the victim based on the least expensive session to roll back.

Additional Information: For general information about the DEADLOCK_PRIORITY option, see the Set Deadlock_Priority MSDN article.
Wait TimeTime in (ms) milliseconds spent waiting on the resource.
Lock ModeThe requested lock mode (e.g. Shared (S), Update (U), Exclusive (X), etc.).

Additional Information: See the Transaction Locking and Row Versioning Guide and Lock Modes articles on Microsoft Docs.
Isolation LevelThe current transaction isolation level.

Additional Information: For general information on isolation levels see the Isolation Levels in the Database Engine MSDN article.
Trans NameName of the associated transaction.

Node Details

Processes

ColumnDescription
TypeStates whether the process is the Owner or Waiter for the lock.
SPID [ecid]The session process ID of the associated owner/waiter.
Lock ModeThe requested lock mode (e.g. Shared (S), Update (U), Exclusive (X), etc.).

Additional Information: See the Transaction Locking and Row Versioning Guide and Lock Modes articles on Microsoft Docs.
HostThe server or workstation name.
ApplicationThe associated application name.
LoginThe user login associated with the session.
Text DataThe associated text data (e.g. T-SQL statement).

Call Stack

ColumnDescription
ObjectThe associated object name.
Line NumberThe line number which was being executed when the lock occurred.
Text DataThe associated text data (e.g. T-SQL statement).

Locks

ColumnDescription
ContextStates whether the lock is held by the Owner or is a Waiter for the lock.
Lock ModeThe requested lock mode (e.g. Shared (S), Update (U), Exclusive (X), etc.).

Additional Information: See the Transaction Locking and Row Versioning Guide and Lock Modes articles on Microsoft Docs.
Lock TypePoints to the lock type, such as a page or object.

Additional Information: See the Lock Granularity and Hierarchies section of the Transaction Locking and Row Versioning Guide on Microsoft Docs.
ObjectThe object involved in the deadlock, such as a table, index, or view name.

Additional Information: See the sys.all_objects (Transact-SQL) article for more information about objects and the Lock Granularity and Hierarchies section of the Transaction Locking and Row Versioning Guide on Microsoft Docs.
IndexIndex associated with the lock (if applicable).
Wait ResourceThe resource associated with the deadlock. This could be the exact SQL Server page of data, for example.

SentryOne Monitor Deadlocks Node Details
Example with a Page Lock and PAGE Wait Resource

Deadlock XML

Select DEADLOCK XML to view the raw XML file for the selected deadlock. Use the Copy button to copy all the XML text to your clipboard.

SentryOne Monitor Deadlocks View XML

Note:  The Copy button is only available when you are using HTTPS (requires an SSL certificate for your SentryOne Portal installation).