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