Documentation forSQL Sentry

SQL Sentry Portal Deadlocks

Applies to the following products and features: The on-premises SQL Sentry Portal feature for SQL Sentry. The Deadlocks tab is available for SQL Server, Azure SQL DB, and Amazon RDS targets. See the Getting Started with SQL Sentry 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. 

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.

Deadlock Diagram example displaying a Page Lock and Page Wait Resource.
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.

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