Find Out What They're Doing with fn_get_sql - Page 4

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!

» See All Articles by Columnist Andrew Novick