Capturing deadlocks in sql profiler Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 82 of sequel server
tutorial in this video we'll discuss how
to capture dead log graph using sequel
profiler to capture the dead log graph
using sequel profiler all you have to do
is add the dead log graph event to the
trace let's look at this in action I
already have sequence of management
studio running here I'm going to click
on tools and select sequel profiler now
we will have to specify the name of the
sequel server and the authentication
credentials to connect to the database
engine on the trace properties window on
the general tab I'm going to select the
blank template and on even selection I'm
going to expand locks section and select
this dead log graph event and then click
the Run button to start a new trace so
the trace is running now at this point
if we execute the code that's going to
cause a deadlock then the information
about that dead log should be captured
by this trace let's look at that in
action now I also have two other
instances of sequence of a management
studio running and we have got two
transactions here I'm going to execute
part of our first transaction and then
part of our second transaction followed
by that I'm going to execute the second
update statement of our first
transaction and finally the second
update statement of transaction two so
in a few seconds we should have a
deadlock so we have a deadlock and this
transactions made a deadlock victim so
we get that 12:05 error the other
transaction has completed successfully
so the information about this deadlock
should have been captured by sequel
profiler notice we have the deadlock
graph now the format of data for this
graph is actually in XML so the
underlying data for this graph is an XML
format now if you want to extract that
XML data into a physical file for later
analysis you can do so by clicking on
file export extract sequel server events
extract deadlock events and then you'll
have to specify a name for your file I'm
going to call this deadlock data and you
know
a deadlock XML file have dot --xtl
extension and then you have the option
to export each event in a separate file
or all events in a single file and then
click the Save button the underlying XML
data should be saved to that physical
file so if we go to the folder notice
that we have a file with dot --xtl
extension we can open this file using
sequence or management studio if you do
that the data will be displayed in a
graphical format like this but if you
want to see the underlying XML data then
you can use any text editor you can use
visual studio or you can use a simple
text editor like notepad let's actually
open this using a notepad and we should
see the underlying XML data and if you
look at this XML data that we have here
it's very similar to the data that we
have captured using sequence of a trace
flag 12:22 we discussed how to use the
sequence of a trace flag 12:22 to
capture deadlock information in one of
our previous video sessions notice it
has got the same set of three sections
the first section here is the dead log
victim section and then we have the
process list section followed by that we
should have a resource list section now
let's analyze this dead log graph now
the Oval on the graph with the blue
cross represents the transactions that
was chosen as the deadlock victim by
sequel server so if you look at the
graph that we have here we've got two
ovals and two rectangles first of all
let's format this graph a little so we
can see it properly so I'm actually
going to zoom this in a bit alright so
now we've got two ovals and two
rectangles the ovals represents the
processes now the oval with the blue
cross represent the transaction that was
chosen as the deadlock victim the other
oval without the blue cross represents
the transaction that completed
successfully
so the oval on the graph the blue cross
represents the transaction that was
chosen as the
dialogue rectum bicycle server The Oval
on the graph without blue cross
represents the transaction that
completed successfully when you move the
mouse pointer over the Oval you can see
the sequel code that was running that
caused the deadlock look at this when I
hover the mouse over we can see the
actual update statement that this
process was executing which caused the
deadlock to happen similarly when I
hover the mouse over I can see the
update statement that this process was
executing the Oval symbols represent the
process nodes and the process nodes
themselves have got a lot of information
for example here we have got the server
process ID look at this the process ID
57 is the one that is chosen as the
deadlock victim and if you look at the
sequence of management studio vendors
here you can see the process ID in the
information bar at the bottom notice
that 55 this process is 55
whereas this process ID is 57 so within
the process ID 57 window we have got
that deadlock error 1205 that means this
is the process ID which is made as the
deadlock victim and if you look at the
graph look at that process ID 57 is made
the deadlock victim and we also have the
information about deadlock priority if
you look at these two transactions we
have not explicitly set any deadlock
priority using set deadlock priority
statement that means both of these
transactions are using the default
deadlock priority which is normal and
for normal deadlock priority the
corresponding integer value is zero so
in both the processes we should see a
deadlock priority as zero and we also
have log used so basically this
represents the transactional log space
used if the transaction has done a lot
of updates then it would have used a lot
of transactional log space that means
obviously to roll that transaction back
there is a lot of cost involved so in
this case what sequel server is going to
do it's going to check the log space use
whichever process has got a lesser
log space used is going to choose that
process as the deadlock back them so
here the log space used is 264 whereas
here the log space used is 292 that's
the reason why this has selected this
process as the deadlock with them the
rectangles represent the resource node
so we also have two rectangles here and
both of these rectangles represent the
resources that are involved in the
deadlock and if you look at that
rectangle they've got something called H
will be tid and H will be T stands for
heap or binary tree ID the very use this
ID is by looking up that ID insisted
partitions view to find the database
object involved in the deadlock so if
you look at the graph here we have got H
would be T ID so we take that ID and
look up that insist or partitions view
and here is the query we have so I'm
taking that H would be t ID and looking
up that insist dot partitions so what
I'm going to do is I'm going to select
all the columns from sistar partitions
and if you look at the columns we have
partition ID object ID index ID etcetera
so what I'm going to do is I'm going to
pass this object ID column value to
object name function which is going to
give us the name of the database object
that's involved in the deadlock so when
I execute this notice that table B is
the underlying database table that's
involved in the deadlock and that's the
query that we use so here the underlying
resource is a table a and here it is
table B because we're using this H OBT
ID here right the ID ends with 630 488
and that's what we see here
so here the underlying table is table B
and here the underlying table is table a
and we also have the index that's
involved so this is the index on table a
and this is the index on table B and the
arrows represents the type of lakhs each
process has on each resource node so if
you look at between these ovals and
these rectangles we have arrows so
basically if you look at you know the
arrow right here so this process 57 is
owning this resource that is table B and
it has already acquired an exclusive
block so this process is the owner for
this resource it has acquired an
exclusive lock on table B and if you
look at process 57 that kind of makes
sense so where do we have this process
57 it is this vendor and here I'll look
at that it's updating table B first so
it has already acquired an exclusive
lock on that and that's what we see here
process 57 has an exclusive lock on
table B on the other hand process 55 is
updating table a first so it has
acquired an exclusive lock on table a
whereas this process B I mean process ID
55 is requesting an exclusive lock on
this resource that is stable be
similarly process 57 is requesting an
exclusive lock on this resource table a
so we have a deadlock situation there so
the graph is much easier to understand
than the raw data thank you for
listening and have a great day