Performance Analysis Deadlocks

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).

SentryOne 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).
    SentryOne Navigator All Targets node
  2. In the Settings pane (View > Settings), select SQL Server from the top drop-down list.
    SentryOne 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.
    SentryOne Settings pane select Deadlocks Source settings
  4. Change the Collect Deadlock Events: Collection setting to False; the setting saves automatically.
    SentryOne 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).
    SentryOne 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.
    SentryOne Settings pane select Deadlocks Source settings
  3. Under Synchronization, change the Inherit From Parent setting to False.
    SentryOne Settings pane change Synchronization settings to False
  4. Change the Collect Deadlock Events: Collection setting to False; it saves automatically.
    SentryOne Settings pane change Collection Settings to False

Deadlock Retention

By default, Performance Analysis Blocking, 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.

SentryOne 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.

SentryOne 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.

SentryOne 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.

SentryOne 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.

SentryOne Deadlocks tab in History mode

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

SentryOne Deadlocks tab Jump To Last Deadlock button

Filter

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

SentryOne 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 of this topic.

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 typeDescriptionImage
SQL ServersFilters deadlocks by selected SQL Server within your environment. SentryOne Deadlocks tab SQL Servers Filter
Show TopDepending 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. SentryOne 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. 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 topic.

SentryOne 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.

SentryOne 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.

SentryOne 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.

SentryOne 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.
    SentryOne Deadlocks select a desired deadlock to display
  2. Select the Play button from the Deadlock Graph toolbar to start the Deadlock Playback.
    SentryOne Deadlocks select play from the deadlock graph toolbar

Deadlock Playback Toolbar

Toolbar OptionDescriptionImage
Play Plays the Deadlock sequence in the order of events. SentryOne Deadlocks Deadlock graph Play button
Skip Forward Goes to the end of the Deadlock event.SentryOne Deadlocks Deadlock graph Skip Forward button
Skip Backwards Goes to the beginning of the Deadlock event.SentryOne Deadlocks Deadlock graph Skip Backwards button
Fast Forward Goes to the next sequence of the Deadlock event.SentryOne Deadlocks Deadlock graph Fast Forward button
RewindGoes to the previous sequence of the Deadlock event.SentryOne Deadlocks Deadlock graph Rewind button
SpeedSets the speed that the Deadlock playback sequence displays. The default speed is 1x. Speed options include: 
  • 1/4x
  • 1/2x
  • 1x
  • 2x
  • 4x
SentryOne Deadlocks Deadlock graph Speed options
Zoom In/ Zoom OutIncrease 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.SentryOne Deadlocks Deadlock graph Zoom toolbar
Auto FitAuto sizes the Deadlock display to fit in the Deadlock graph area.
Layout TypeSelect the Deadlock graph display. Display options include:
  • Circular
  • Force Directed
  • Layered Digraph
SentryOne Deadlocks Deadlock Graph Circular layoutCircular Deadlock Graph Layout
SentryOne Deadlocks Deadlock graph Force Directed layoutForce Directed Deadlock Graph Layout
SentryOne Deadlocks Deadlock graph Layered Digraph layoutLayered Digraph Deadlock Graph Layout
Optimize LayoutConfigures the best layout for the Deadlock graph.

Additional Options

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

FeatureDescriptionImage
Jump to CalendarOpens the selected event on the Event Calendar (requires server to be watched with EM)SentryOne Deadlocks tab Jump To Calendar context menu
Jump to Top SQLOpens the Top SQL tab highlighting the query involved in the deadlock event.SentryOne Deadlocks tab Jump To Top SQL context menu
Export Deadlock to XMLExports the deadlock in XML format.SentryOne Deadlocks tab Export Deadlock to XML context menu
Page dividerThe divider between each of these panes that can be dragged to resize or hide each pane.SentryOne Deadlocks tab Drag or Hide page divider


Note:  Open exported Deadlock XML files (*.xdl) in the SentryOne 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

NameDescription
SQL ServerThe SQL Server where the deadlock took place.
TimeThe time the deadlock occurred.
Victim SPIDThe session process ID of the victim involved in the deadlock.
Victim HostThe workstation belonging to the victim thread.
Victim ApplicationThe application name belonging to the victim thread.
Victim DatabaseThe name of the database on which the process took place
Victim Text DataThe associated text data of the victim.
Deadlock XMLThis 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.

NameDescription
SPID [ecid]The session process ID of the associated owner/waiter.
PlanUses the Plan button to open a Plan Explorer session for the associated query plan. 
HostThe workstation name.
ApplicationThe associated application
DatabaseThe associated database.
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. 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.
Transaction Start TimeTime that the transaction began.
Last Batch Start TimeThe last time a client process started batch execution.
Last Batch Completion TimeThe last time a client process completed batch execution.
Mode/TypeThe mode/type designates the resource lock mode. For general information on Lock modes see the Lock modes MSDN article.
StatusState of the task.
Isolation LevelThe current transaction isolation level. For general information on isolation levels see the Isolation Levels in the Database Engine MSDN article.
Login NameThe Login Name associated with the session.

Owner/Waiter Details

NameDescription
ObjectIndicates the associated object name.
Line NumberThe line number which was being executed when the lock occurred.
Text DataThe associated text data.