Leave Batch B-4 running and switch back to the connection
with Script A. Batch A-5 uses the sp_lock system stored procedure to show
the locks being held by the system. The exclusive locks (Mode = X) held by
Script A are highlighted in red and the wait for a shared lock (Mode = S) is
highlighted in blue.
-- Batch A-5 sp_lock shows who's waiting and who's locking
PRINT 'Batch A-5 -- Output of sp_lock'
Batch A-5 -- Output of sp_lock
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
53 5 1977058079 0 TAB IX GRANT
53 5 1977058079 1 PAG 1:127 IX GRANT
53 5 1977058079 1 KEY (0801c4f7a625) X GRANT
53 5 0 0 DB S GRANT
53 5 1977058079 1 PAG 1:239 IX GRANT
53 5 1977058079 2 KEY (1f048d178a34) X GRANT
53 1 85575343 0 TAB IS GRANT
54 14 0 0 DB S GRANT
55 5 1977058079 1 PAG 1:127 IS GRANT
55 5 0 0 DB S GRANT
55 5 1977058079 0 TAB IS GRANT
55 5 1977058079 1 KEY (0801c4f7a625) S WAIT
The explanation of what is happening is that SPID 55, which
is running Batch B-4, is waiting for a shared lock on Key 0801c4f7a625. However,
SPID 53 has been granted an exclusive lock on that key. Had we set the
transaction isolation level in Batch B-4 to READ UNCOMMITTED, Batch B-4 would
not have requested the shared lock and would not have to wait.
Finally, it is time to use fn_get_sql to examine the SQL
that Batch B-4 is running. This is done with Batch A-6. Before you can run
A-6, you must change the line "WHERE spid=55" to replace the 55
with the SPID that was printed by Batch B-3. Here is Batch A-6 with its
-- Batch A-6 You must change the SPID number in this batch
-- before executing this step!
PRINT 'Batch A-6 -- Get the text of the blocked connection'
DECLARE @Handle binary(20)
WHERE spid= 55 -- <<<<<< Change 55 to the SPID of Script B
SELECT * FROM ::fn_get_sql(@handle)
ROLLBACK TRAN -- Releases the lock on authors
Batch A-6 -- Get the text of the blocked connection
------ ----------- ------ --------- ------------------
NULL NULL NULL 0 -- Batch B-4
PRINT 'Batch B-4 SELECT a blocked resource.'
select * from authors
(1 row(s) affected)
The [text] column has carriage returns in it and they show
up in the output. To make it easier to see the results, I have highlighted the
output of the text column in blue. Since there were three lines in the batch,
it wraps onto a second and third line of output.
The last line of A-6 is a ROLLBACK TRAN statement. This
undoes the effect of the DELETE done earlier. It also has the effect of
releasing the exclusive locks that are held by Script A's connection. If you
flip back to Script B, you'll see that it has run and sent its output to the
fn_get_sql is a new function to aid the DBA and programmer
in the diagnosis of blocking problems. It can also be put to use by diagnostic
or performance monitoring tools to monitor the SQL being run by any process in
the system. That would be done by continually sampling the SQL of all
processes to discover the statements that are executed most often. I am aware
of at least one tool on the market that is using it in this way. However, you don't
need an expensive tool to put fn_get_sql to good use. A simple script, like
the one in batch A-6 that gets a sql_handle and uses it is all you need.
You've read the article, now watch the movie!
See All Articles by Columnist Andrew Novick