Database Reference
In-Depth Information
Deadlock Analysis
You can sometimes prevent a deadlock from happening by analyzing the causes. You need the following information
to do this:
•
The sessions participating in the deadlock
•
The resources involved in the deadlock
•
The queries executed by the sessions
Collecting Deadlock Information
You have four ways to collect the deadlock information.
•
Use Extended Events
•
Set trace flag 1222
•
Set trace flag 1204
•
Use trace events
Trace flags are used to customize certain SQL Server behavior such as, in this case, generating the deadlock
information. But, they're an older way to capture this information. Within SQL Server, on every instance since 2008,
there is an Extended Events session called
system_health
. This session runs automatically, and one of the events
it gathers by default is the deadlock graph. This is the easiest way to get immediate access to deadlock information
without having to modify your server in any way.
However,
system_health
is only good for spot checks. And since it uses the
ring_buffer
to capture data, unless
you're looking at it immediately after experiencing a deadlock, you may find that the information is missing. If
you need to gather information for longer periods of time and ensure that you capture as many events as possible,
Extended Events provides several ways to gather the deadlock information. This is probably the best method you can
apply to your server for collecting deadlock information. You can use these options:
Lock_deadlock
: Displays basic information about a deadlock occurrence
•
Lock_deadlock_chain
: Captures information from each participant in a deadlock
•
Xml_deadlock_report
: Displays an XML deadlock graph with the cause of the deadlock
•
The deadlock graph generates XML output. After Extended Events captures the deadlock event, you can view
the deadlock graph within SSMS either through the event viewer or by opening the XML file if you output your event
results there. While similar information is displayed in all three events, for basic deadlock information, the easiest to
understand is the
xml_deadlock_report
. When monitoring for deadlocks, I recommend also capturing the
lock_deadlock_chain
so that you have more detailed information about the individual sessions involved in the
deadlock if you need it.
You can open the deadlock graph in Management Studio. You can search the XML, but the deadlock graph
generated from the XML works almost like an execution plan for deadlocks, as shown in Figure
21-2
.
