Export (0) Print
Expand All
Expand Minimize

Analyze Deadlocks with SQL Server Profiler

Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, 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. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure SQL Server Profiler 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. Note that this tab does not 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.

SQL Server Profiler and SQL Server Management Studio 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:

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

Server process identifier (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 SPID.

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 (Transact-SQL).

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.

Community Additions

ADD
Show:
© 2014 Microsoft