When DBCC INPUTBUFFER disappoints

Feb 6, 2012 / By Hemantgiri Goswami

Tags: , ,

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)

You must be thinking why I have used this function instead sys.dm_exec_sql_text? Any guess??
Yes, you are right, customer is still using SQL server 2000 {Winking smile}
TIP: If you happened to face similar situation but on SQL Server 2005 or SQL Server 2008 and greater, there are scripts that you can use :

Erland Sommarskog, SQL Server MVP has written aba_lockinfo and a script a.k.a. Custom Blocker Report from Aaron Bertrand.

-- Hemantgiri S. Goswami

3 Responses to “When DBCC INPUTBUFFER disappoints”

  • Moham Mawla says:

    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

  • goswami says:

    Hi Mohammed,

    I will update blog post to reflect your suggestion, and update code also to filter only ‘runnable’

  • goswami says:

    Hi Aaron,

    I will highlight and update my blog post so that readers can see that this is for SQL 2000 :)

    Thank you

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>