-
-
This is certainly not a new topic but something that i felt was
worth revisiting and reminding people about, especially because i find
many people are not aware of the ability to extract the statement
within a stored procedure being executed.
So, prior to SQL 2000 SP3, when we want to know what code a SPID is executing we have to use DBCC INPUTBUFFER ().
The problem with this command is that its restricted to the first 256
characters and only returns the outermost statement executing. That
means that if we have stored procedure 1 calling stored procedure 2 the
input buffer will only tell us about stored procedure 1 and it may even
get truncated!
I clearly remember reading the SP3 readme and thinking Christmas has
come early as i found the entry about a new function being
introduced called ::fn_get_sql. I've got to say right now this is the
best present yet! It has made trouble shooting so much easier. Why?
Well read on.
Running the code below will now show you the inner most statement
executing and we now get up to 4000 characters. In the case of a stored
procedure you see the whole procedure.
DECLARE @Handle binary(252)
SELECT @Handle = sql_handle FROM master..sysprocesses WHERE spid = 109 --CHANGE SPID NUMBER
SELECT * FROM ::fn_get_sql(@Handle)
The
part that really got me? When it returns the code of a stored procedure
you could be left wondering what statement within the stored procedure
is executing. Fear not! They have that covered to. All you have to do
is run the modified code below and the exact statement causing you a
problem is identified.
DECLARE @Handle binary(20), @start int, @end int
SELECT @Handle = sql_handle, @start = stmt_start, @end = stmt_end
FROM master..sysprocesses WHERE sql_handle <> 0x00 AND spid = 226 --CHANGE SPID NUMBER
IF NOT EXISTS (SELECT * FROM ::fn_get_sql(@Handle)) PRINT 'Handle not found in cache'
ELSE
SELECT 'Current statement'=
substring(text, (@start + 2)/2,
CASE @end
WHEN -1 THEN (datalength(text))
ELSE (@end -@start + 2)/2
END)
FROM ::fn_get_sql(@Handle)
So, now i've reminded you of this really useful tool its time for a few "Battle Tips".
1) Make sure in query analyser your "maximum characters per column" is set to 4000. Otherwise you are only going to get 256....
2)
If SQL generates a "zero cost plan" it will not put it in cache
because its not worth it. As its not in cache ::fn_get_sql won't return
any information.
3) This one comes with a health warning..... If for
some reason you need SQL to cache the zero cost plans so that
::fn_get_sql will always work then you can enable trace flag 2861. I
would recommend you do not use this in production as the overhead is
substantial and profiler should be used instead but some people might
find it beneficial on a dev box.
4) I've not adjusted the script
above to deal with parallel spids where you get multiple handles for 1
spid. Just a note, i keep on forgetting. :D
There are many uses for this function and i encourage you to explore
them. SQL 2005 makes life even easier through DMV's but thats another
blog......