Documentation forSQL Sentry

Plan Explorer Deadlock Files



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.

Opening a Deadlock File

SQL Sentry Deadlock File

After you've captured a deadlock file (.xdl), open it with Plan Explorer just as you would a plan file. 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 Graph with representations for each process and resource involved in the deadlock.

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 Graph

The Deadlock Graph 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.

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.
Deadlock XML 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.
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.