| title | Analyze Deadlocks | |
|---|---|---|
| titleSuffix | SQL Server Profiler | |
| description | Identify the cause of a deadlock by replaying and displaying deadlock events for analysis in SQL Server Profiler and by generating wait-for graphs. | |
| author | rwestMSFT | |
| ms.author | randolphwest | |
| ms.date | 06/05/2025 | |
| ms.service | sql | |
| ms.subservice | profiler | |
| ms.topic | concept-article | |
| ms.collection |
|
[!INCLUDE SQL Server Azure SQL Managed Instance]
Use [!INCLUDE ssSqlProfiler] to identify the cause of a deadlock. A deadlock occurs when there's a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using [!INCLUDE ssSqlProfiler], you can create a trace that records, replays, and displays deadlock events for analysis.
To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. [!INCLUDE ssSqlProfiler] can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure [!INCLUDE ssSqlProfiler] to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files. This extraction can be done in any of the following ways:
-
At trace configuration time, using the Events Extraction Settings tab. This tab doesn't appear until you select the Deadlock graph event on the Events Selection tab.
-
Using the Extract SQL Server Events option on the File menu.
-
Individual events can also be extracted and saved by right-clicking a specific event and choosing Extract Event Data.
[!INCLUDE ssSqlProfiler] and [!INCLUDE ssManStudioFull] use a deadlock wait-for graph to describe a deadlock. The deadlock wait-for graph contains process nodes, resource nodes, and edges representing the relationships between the processes and the resources. The components of wait-for graphs are defined in the following table:
| Node | Description |
|---|---|
| Process node | A thread that performs a task; for example, INSERT, UPDATE, or DELETE. |
| Resource node | A database object; for example, a table, index, or row. |
| Edge | A relationship between a process and a resource. A request edge occurs when a process waits for a resource. An owner edge occurs when a resource waits for a process. The lock mode is included in the edge description. For example, Mode: X. |
In a wait-for graph, the process node contains information about the process. The following table explains the components of a process.
| Component | Definition |
|---|---|
| Server process ID | Session ID (SPID), a server assigned identifier for the process owning the lock. |
| Server batch ID | Server batch identifier (SBID). |
| Execution context ID | Execution context identifier (ECID). The execution context ID of a given thread associated with a specific session ID. ECID = { 0, 1, 2, 3, ...n }, where 0 always represents the main or parent thread, and { 1, 2, 3, ...n } represent the subthreads. |
| Deadlock priority | Deadlock priority for the process. For more information about possible values, see SET DEADLOCK_PRIORITY. |
| Log Used | Amount of log space used by the process. |
| Owner Id | Transaction ID for the processes which are using transactions and currently waiting on a lock. |
| Transaction descriptor | Pointer to the transaction descriptor that describes the state of the transaction. |
| Input buffer | Input buffer of the current process, defines the type of event and the statement being executed. Possible values include: Language RPC None |
| Statement | Type of statement. Possible values are: NOP SELECT UPDATE INSERT DELETE Unknown |
In a deadlock, two processes are each waiting for a resource held by the other process. In a deadlock graph, the resources are displayed as resource nodes.