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

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

SPID [ecid]The session process ID of the associated owner/waiter.
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.

Additional Information: For more information, 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.

Additional Information: For more information about lock mode, see the SQL Server Transaction Locking and Row Versioning Guide MSDN article. 
StatusState of the task.
Isolation LevelThe current transaction isolation level.

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

Owner/Waiter Details

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