How to find blocking queries in sql server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 86 of sequel server
tutorial in this video we'll discuss how
to find a blocking queries in sequel
server blocking occurs if there are open
transactions let's understand this with
an example here we have two instances of
sequence of management studio on the
Left we have a transaction I'm going to
execute only part of this transaction
notice I have not executed the commit
statement that means we have an open
transaction here and on the right we
have a few queries that touch the same
table that this transaction on the Left
is updating now let's see what's going
to happen if we try to select the count
from the table notice the query is
naturally blocked and that's because we
have an open transaction now let's
cancel this query and try to delete that
row from the table and look at that the
same block now let's try to truncate the
table and finally try to drop the table
so all these queries are affected by
this open transaction all of them are
blocked now let's see what's going to
happen when we complete the transaction
by executing the commit statement at
this point if we try to select the count
from the table we should not have any
problem basically all of these queries
should succeed so the obvious next
question that comes to our mind is how
to find the blocking queries for that we
will have to find the open transactions
now in this case it's easy to find the
open transaction because we have started
this transaction but in real world there
will be multiple users connected to the
database and we don't know what queries
they're executing and what transactions
they have left to open now if we try to
execute queries and if they are blocked
then the most likely cause for that
might be you know an open transaction so
we are definitely interested in finding
out open transaction one way to find
open transactions is by using sequel
server dbcc open Tran command
the problem with this command is that it
will only display the oldest active
transaction it's not going to list all
the open transactions let's look at that
in action so
at the moment we don't have any open
transaction so when we execute dbcc
open Tran it should say no active open
transactions now let's execute part of
this transaction and now let's execute
dbcc open channel look at that oldest
active transaction and it gives us the
process ID that has executed the
transaction and the process ID is 54 and
if you look at this window here notice
the process ID here is 54 so this has
got that oldest active transaction now
let's try to select count of star from
table B so now we get the data without
any block but let's try to execute
another transaction on that table so
here you know in another window I have a
transaction let's only execute part of
this transaction so we have another open
transaction at this point if I try to
select count of star from table B that
will be blocked and then to find the
open transaction if I use DBC see open
Tran so let's execute cancel this and
execute dbcc open Tran so it gives
information about process ID 54 and if
you look at process ID 54 it's updating
table a and I'm trying to get the count
from table B so here the oldest active
transaction is not the transaction
that's blocking my query so it's some
other open transaction but you know
that's not the oldest open transaction
so dbcc will not be able to provide
information about that now the obvious
next question that comes to our mind is
is it possible to list all the open
active transactions absolutely on the
web on sequel skills com I have found
the script which is extremely useful and
provides valuable information about open
transactions so let's copy that and
execute that right here so let's
maximize this window
and let's execute this query so now look
at that it gives a lot more information
about those open transactions first of
all it gives us the session IDs 54 and
56 and it gives us the login name
associated with that processes so we can
use that login ID and then contact that
person and ask them to complete the
transaction right and we also have the
transaction begin time and the beauty is
we also have the actual sequel script
the transaction is executing look at
that process 54 is executing in are
basically this sequel which is updating
table a so process 54 as you can see is
updating table a and process 56 which is
this one here is updating table B and if
you look at your query here we are
trying to select count of star from
table B and if you look at process 56 is
the one that's affecting your query so
you will contact this person and ask him
to complete in order transaction now if
the person is not available you know you
also have the option to kill the process
but killing the process may have
unintended consequences so use this
option with extreme caution now the
obvious next question that comes to our
mind is how to kill the process again
there are two ways to do that one is by
using sequel server Activity Monitor and
to get to the Activity Monitor within
the object Explorer right-click on the
server instance and then select Activity
Monitor so this is going to display this
Activity Monitor vendor expand the
processes so it should list all the
sequel server processes and the
processor that we are interested in is
56 that's what we want to kill and to
kill it from the active witty monitor go
to that process right click on that and
you have the option to kill that process
so let's kill it
now when we go back and execute this
query it should succeed without any
problem look at that the process is
killed now let's run this query once
again so now we only have one active
transaction and that is with process ID
54 now one way to kill the process is by
using Activity Monitor the other way by
is by using sequel command kill and then
we specify the process ID so let's go
ahead and do that for process ID 54 so
I'm going to use kill and the process ID
is 54 execute that command completed
successfully and when we execute this
query now we should get no open
transactions so what actually happens
when we kill a session all the work that
the transaction has done will be rolled
back basically the transaction is ruled
back that means the database must be put
back in the state it was in before the
transaction started that's why it is
rolling back that transaction when we
kill a session thank you for listening
and have a great day