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' exec sp_lock GO (Results) 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 results:
-- 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) SELECT @handle=sql_handle FROM master..sysprocesses WHERE spid= 55 -- <<<<<< Change 55 to the SPID of Script B SELECT * FROM ::fn_get_sql(@handle) ROLLBACK TRAN -- Releases the lock on authors GO (Results) 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 results window.
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!