Documentation forSQL Sentry

Plan Explorer Deadlock Files

Introduction

Plan Explorer allows you to open deadlock files. Deadlock (.xdl) files can be captured or collected from several places, including the following:

  • SQL Sentry Performance Analysis—Captures deadlocks happening in your monitored environment. These .xdl files can be exported and then shared.
  • SQL Server Profiler—Allows you to create a trace that captures deadlock events. 
Additional Information: For more information about SQL Server Profiler, see the Save Deadlock Graphs MSDN article.

With Version 2024.1, you can copy deadlock XML directly into a new deadlock file session.

Open a Deadlock File

SQL Sentry Deadlock File

You can open a deadlock (.xdl) file by clicking File > Open, and then selecting the desired deadlock file from the file explorer.

To create a new deadlock file session: Click File > New > Deadlock. Paste in any deadlock xml using CTRL+V, or click the Paste button.

A deadlock displays in two main sections:

  • The top section contains a Deadlock Grid view that breaks down the deadlock by SPID and lock resource. 
  • The bottom section contains the Deadlock Diagramtab with representations for each process and resource involved in the deadlock, and the XML tab with the raw deadlock xml.

Deadlock Grid View

In the Deadlock Grid view each record is expandable to view the details regarding the processes that were involved in the deadlock. For more information about the metrics displayed, see the Deadlock Metrics section, following.

  • The deadlock victim is highlighted with a red background.

Deadlock Diagram

The Deadlock Diagram is formed according to the 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 (one) 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.
  • Selecting object nodes in the graphical representation sets focus to that object in the Grid view.
  • Zoom in and out on the Deadlock Graph using Ctrl + mouse wheel, or through the right-click context menu.
  • Drag and rearrange nodes as desired.

See the table below for information about the Deadlock Diagram playback options.

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.

Deadlock XML

Click the XML tab at the bottom of the Deadlock Diagram to open the raw xml for your deadlock.

Deadlock Metrics

Deadlock Overview

Name Description
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.

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.
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 more information, 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 more information about lock mode, see the SQL Server Transaction Locking and Row Versioning Guide MSDN article. 
Status State of the task.
Isolation Level The current transaction isolation level.

Additional Information: For more information on isolation levels, see the SQL Documentation MSDN article. 
Login Name The Login Name associated with the session.
Lock Partition The table partition(s) involved in the deadlock. 
Request Type The type of request, including wait, lock, or convert. 

Owner/Waiter Details

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