When DBCC INPUTBUFFER disappoints

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