When DBCC INPUTBUFFER disappoints
Feb 6, 2012 / By Hemantgiri Goswami
Today we have an alert from one of the client server about blocking, I have immediately start looking at it using below statement
SELECT * FROM MASTER..SYSPROCESSES WHERE BLOCKED != 0;
Yes, I’ve found the culprit SPID that is occupying more resources, using DBCC INPUTBUFFER(SPID). so, instead of
seeing what exactly this SPID is doing I have received SP_EXECUTESQL, surprised!!! No, it’s bound to happen when
SPID is running dynamic TSQL (using SP_EXECUTESQL) and/or cursor. But, yes, at the same time I would like to
know what it is running behind the scene. There are two options that came to my mind
- Using Profiler
- Using function – ::fn_get_sql(@SQLHandle)
I preferred to use 2nd option as this is one time effort (at least as of now), and, it would be very quick. So, here is what I have used
-- Variable that will store the SQLHandle DECLARE @SQLHandle BINARY(20) -- Variable that will pass on the culprit SPID DECLARE @SPID INT -- value for culprit SPID SET @SPID = 52 -- this will give you the SQLHandle for the culprit SPID SELECT @SQLHandle = SQL_HANDLE FROM MASTER..SYSPROCESSES WHERE SPID = @SPID -- this statement will give you the SQL Statement for culprit SPID SELECT [TEXT] FROM ::FN_GET_SQL(@SQLHandle)
Erland Sommarskog, SQL Server MVP has written aba_lockinfo and a script a.k.a. Custom Blocker Report from Aaron Bertrand.
-- Hemantgiri S. Goswami
Please note that in SQL Server 2005 and above you should be using the sys.dm_exec_* DMVs instead of sysprocesses, since the latter is only there for backward compatibility reasons. Also the :: notation for calling functions has been deprecated and should also be abandoned – in this case you can simply cross apply with the function sys.dm_exec_sql_text().
I realize you say that the customer is using SQL Server 2000, I just don’t expect that all readers will catch this and may just grab the code without reading the fine print.
FN_GET_SQL can disappoint too and it did for me a week ago ; it can do in some cases
- If the blocking process is sleeping or in background
- If the plan cost is low and was flushed of the cache.
In my case , it was also a 3rd party app using server-side API cursors, something implemented by drivers such as ODBC , JDBC
It’s a bad combination anyway with 12-years old SQL 2000
Hi Mohammed,
I will update blog post to reflect your suggestion, and update code also to filter only ‘runnable’
Hi Aaron,
I will highlight and update my blog post so that readers can see that this is for SQL 2000 :)
Thank you