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.
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 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).
To disable the deadlock collection globally, complete the following steps:
To disable the deadlock collection for an individual instance complete the following steps:
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.
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.
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.
Display / Controls
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.
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.
Alternatively, use the Jump To Last Deadlock button to navigate to the last deadlock.
The Top pane provides client filters for specifying which records to display for the selected interval.
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:
|SQL Servers||Filters deadlocks by selected SQL Server within your environment.|
|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.|
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.
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.
- 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.
Zoom in and out on the Deadlock Graph using CTRL + Mouse Wheel or through the right-click context menu.
Deadlock Playback Functionality
Playback the sequence of events for a selected Deadlock by completing the following:
- Select a desired Deadlock in the Grid view to display the Deadlock in the Deadlock Graph.
- Select the Play button from the Deadlock Graph toolbar to start the Deadlock Playback.
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.
|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: |
|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:|
|Optimize Layout||Configures the best layout for the Deadlock graph.|
The following options are available through the right-click context menu:
|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.
|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.|
The Lock Details area breaks down the deadlock by specific lock types, including the owners and waiters involved in each lock.
|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.|
|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.|