Documentation forSQL Sentry

Performance Analysis Deadlocks

Applies to:  The Deadlocks tab is available for SQL Server, SSAS, Azure SQL Database, and AWS RDS targets.
SQL Sentry Portal: This feature is available in SQL Sentry Portal. To learn more about configuring your environment to use the on-premises, browser-based option with your existing SQL Sentry database, see the SQL Sentry Portal article.
See the SQL Sentry Portal Deadlocks article for an example of the Deadlocks feature in a browser.

Overview

The Deadlocks tab displays information about deadlocks happening within your environment. Use the Deadlocks tab to identify and correct deadlocks on your monitored servers.

Deadlock Collection

Deadlock collection is controlled through the Deadlock Source settings. The Collect Deadlock Events setting is True by default, so whenever a new SQL Server instance is watched, deadlock events are automatically collected for that instance. Adjust the deadlock collection as desired through the Settings pane (View menu > Settings).

Settings pane Deadlocks Source settings

Example One

To disable the deadlock collection globally, complete the following steps:

  1. Select All Targets (globally ) in the Navigator pane (View > Navigator > All Targets).
    Navigator All Targets node
  2. In the Settings pane (View > Settings), select SQL Server from the top drop-down list.
    Settings pane select SQL Server Settings
  3. Select Deadlocks Source from the second drop-down list to open the Deadlocks Source settings that are being applied globally. Settings pane select Deadlocks Source settings
  4. Change the Collect Deadlock Events: Collection setting to False; the setting saves automatically.
    SQL Sentry Settings pane change Collection Settings to False

Example Two

To disable the deadlock collection for an individual instance complete the following steps:

  1. Select the desired instance node in the Navigator pane (View > Navigator > desired instance).
    Select desired instance from the Navigator
  2. In the Settings pane (View > Settings), select Deadlocks Source from the second drop-down list to open the Deadlocks Source settings for the instance. Settings pane select Deadlocks Source settings
  3. Under Synchronization, change the Inherit From Parent setting to False.
    SQL Sentry Settings pane change Synchronization settings to False
  4. Change the Collect Deadlock Events: Collection setting to False; it saves automatically.
    Settings pane change Collection Settings to False

Deadlock Retention

By default, Performance AnalysisBlocking, Top SQL, and Deadlock data is retained for 15 days. This is controlled with the Keep Performance History For Setting found on the Storage tab of Global Settings (Navigator pane > Configuration> Global Settings > Storage tab). For more information about retention, see the Data Capacity Planning topic.

SQL Sentry Monitoring Service Settings Keep Event History For

Deadlock Alerting

Configure actions in response to two SQL Server deadlock conditions. The SQL Server: Deadlock condition occurs each time there's a deadlock in your environment. There are numerous actions that you can take in response to this condition occurring.

Actions Selector SQL Server: Deadlock

Configure an action in response to the SQL Server Deadlock: Output Content Match (OCM) condition through the Conditions pane (View > Conditions). The OCM condition is useful in narrowing the notifications you see concerning deadlocks. The OCM condition uses the entire deadlock XML to match against, making it a powerful tool in pinpointing specific deadlocks.

Actions Selector SQL Server: Deadlock: Output Content Match

Display / Controls

Toolbar

The Deadlocks tab has two modes, Real Time and History. The active mode is controlled through the toolbar. When the auto-refresh Play button is selected, the tab enters Real Time mode. In Real Time mode, any deadlocks that happened within the last 10 minutes display.

Deadlocks tab in Real-time mode

When the auto-refresh Pause button is selected, the tab goes into History mode. Select a time range and then use the Go toolbar button to view deadlocks that happened during that time period.

Deadlocks tab in History mode

Alternatively, use the Jump To Last Deadlock button to navigate to the last deadlock.

Deadlocks tab Jump To Last Deadlock button

Filter

The Top pane provides client filters for specifying which records to display for the selected interval.

Deadlocks tab top pane Filter

Note:  The Filter pane is for client side filtering only and doesn't impact what's collected. For more information, see the Deadlock Collection section in this article.

After changing any of the filters, it's necessary to select Refresh, or press F5, to apply the filter. To select more than one item in a list, use Shift-Click or CTRL-Click. The available filters are as follows:

Filter type Description Image
SQL Servers Filters deadlocks by selected SQL Server within your environment.  Deadlocks tab SQL Servers Filter
Show Top Depending on the length of the displayed interval, you may have a lot of data to work with. This setting controls how many of the rows are retrieved.  Deadlocks tab Show Top Filter

Deadlock Gridview

The Deadlock Gridview lists the records matching the current filters. The default filter displays all records. Each record is expandable to view the details regarding the processes that were involved in the deadlock. 

Additional Information: For general background information about what constitutes a SQL Server deadlock, see the Deadlocking MSDN articles.

For general information about the metrics displayed, see the Deadlock Metrics section at the end of this article.

Deadlocks tab Deadlock Gridview

Deadlock Graph

The Deadlock Graph is formed according to the captured deadlock XML. Process and Resource nodes are independently represented, along with any relationships that exist between them.

Deadlocks Graph diagram

  • The deadlock victim is highlighted with a red background.
  • The numbers and associated arrows between each node indicate the actual sequence of events that took place, leading to the deadlock.
  • The letters indicate the requested Lock mode.

Using the Deadlock Graph 

Select object nodes in the graphical representation to focus on that object in the Gridview.

Deadlocks tab select the Deadlock Graph

Zoom in and out on the Deadlock Graph using CTRL + Mouse Wheel or through the right-click context menu.

Deadlocks tab Zoom In on Deadlock Graph

Deadlock Playback Functionality

Playback the sequence of events for a selected Deadlock by completing the following:

  1. Select a desired Deadlock in the Grid view to display the Deadlock in the Deadlock Graph. SQL Sentry Deadlocks select a desired deadlock to display
  2. Select the Play button from the Deadlock Graph toolbar to start the Deadlock Playback. Deadlocks select play from the deadlock graph toolbar

Deadlock Playback Toolbar

Note:  Many of the deadlocks in the table below show an orange dotted line. This indicates that a lock was requested, but could not be granted. When the arrow points from the resource to the lock, it wasn't granted. When the arrow points from the lock to the resource, it was granted.
Toolbar Option Description
Play  Plays the Deadlock sequence in the order of events. 
Skip Forward  Goes to the end of the Deadlock event.
Skip Backwards  Goes to the beginning of the Deadlock event.
Fast Forward  Goes to the next sequence of the Deadlock event.
Rewind Goes to the previous sequence of the Deadlock event.
Speed Sets the speed that the Deadlock playback sequence displays. The default speed is 1x. Speed options include: 
  • 1/4x
  • 1/2x
  • 1x
  • 2x
  • 4x
Zoom In/ Zoom Out Increase or decrease the size of the displayed Deadlock graph. Use the + or - symbols to zoom in or out, or click and drag the zoom toolbar.
Auto Fit Auto sizes the Deadlock display to fit in the Deadlock graph area.
Layout Type Select the Deadlock graph display. Display options include:
  • Circular
  • Force Directed
  • Layered Digraph
Optimize Layout Configures the best layout for the Deadlock graph.

Additional Options

The following options are available through the right-click context menu:

Feature Description
Jump to Calendar Opens the selected event on the Event Calendar (requires server to be watched with EM).
Jump to Top SQL Opens the Top SQL tab highlighting the query involved in the deadlock event.
Export Deadlock to XML Exports the deadlock in XML format.
Page divider The divider between each of these panes that can be dragged to resize or hide each pane.
Note:  Open exported Deadlock XML files (*.xdl) in the SQL Sentry client (File > Open). Whenever you open a deadlock, a new tab displaying the deadlock information, including the deadlock graph, is created.

Deadlock Metrics

Deadlock Overview

Name Description
SQL Server The SQL Server where the deadlock took place.
Time The time the deadlock occurred.
Victim SPID The session process ID of the victim involved in the deadlock.
Victim Host The workstation belonging to the victim thread.
Victim Application The application name belonging to the victim thread.
Victim Database The name of the database on which the process took place
Victim Text Data The associated text data of the victim.
Deadlock XML This is the actual captured Deadlock XML.

Lock Details

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

Name Description
SPID [ecid] The session process ID of the associated owner/waiter.
Plan Uses the Plan button to open a Plan Explorer session for the associated query plan. 
Host The workstation name.
Application The associated application
Database The associated database.
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.
Transaction Start Time Time that the transaction began.
Last Batch Start Time The last time a client process started batch execution.
Last Batch Completion Time The last time a client process completed batch execution.
Mode/Type The mode/type designates the resource lock mode. Additional Information: For general information on Lock modes see the Lock modes MSDN article.
Status State of the task.
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.
Login Name The Login Name associated with the session.

Owner/Waiter Details

Name Description
Object Indicates the associated object name.
Line Number The line number which was being executed when the lock occurred.
Text Data The associated text data.