SQL Sentry Portal Deadlocks

Deadlocks

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

Select a SQL Server, Azure SQL DB, or Amazon RDS target and then select Deadlocks to open the Deadlocks view.

Portal Deadlocks grid organized by Time with the most recent events at the top of the list.

Note: Deadlocks for Amazon RDS are not supported in SentryOne Monitor or the on-premises SQL Sentry 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.

Deadlock Diagram displaying the deadlock victim, process, and keylocks in numerical order and Node details for the deadlock.

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

Column	Description
SPID [ecid]	The session process ID of the associated owner/waiter.
Host	The server or workstation name.
Application	The associated application (e.g. a SQLAgent Job, .Net SqlClient Data Provider, name of a specific application running SQL statements against the associated database, etc.).
Database	The associated database.
Login	The user login associated with the session.
Log Used	The amount of log space used by the process.
Deadlock Priority	Specifies 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 Time	Time in (ms) milliseconds spent waiting on the resource.
Lock Mode	The 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 Level	The current transaction isolation level. Additional Information: For general information on isolation levels see the Isolation Levels in the Database Engine MSDN article.
Trans Name	Name of the associated transaction.

Node Details

Processes

Column	Description
Type	States whether the process is the Owner or Waiter for the lock.
SPID [ecid]	The session process ID of the associated owner/waiter.
Lock Mode	The 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.
Host	The server or workstation name.
Application	The associated application name.
Login	The user login associated with the session.
Text Data	The associated text data (e.g. T-SQL statement).

Call Stack

Column	Description
Object	The associated object name.
Line Number	The line number which was being executed when the lock occurred.
Text Data	The associated text data (e.g. T-SQL statement).

Locks

Column	Description
Context	States whether the lock is held by the Owner or is a Waiter for the lock.
Lock Mode	The 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 Type	Points 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.
Object	The 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.
Index	Index associated with the lock (if applicable).
Wait Resource	The resource associated with the deadlock. This could be the exact SQL Server page of data, for example. 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.

Deadlock XML displaying the deadlock in color coded XML with the option to copy the XML to the clipboard.