When DBCC INPUTBUFFER disappoints

Posted in: Technical Track

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

Interested in working with Hemantgiri? Schedule a tech call.

About the Author

I am a Database Administrator by profession, and a student of a university called life by heart. I am open and willing to learn things as it comes and is interest to me. I am passionate about SQL Server, photography, reading and sharing. During my professional life, I have privileged to work with some great a-like-minded people from whom I learnt a lot, technical as well as soft skills; I am trying and will continue to share what all I have learnt over the years by connecting a-like-minded via many technological forums.Currently, I'm Lead Database Consultant @Pythian. I have close to 17 years of experience, about which 15 years as hard core DBA. I have been a Microsoft SQL Server MVP for four years, and a published author of the book - SQL Server 2008 High Availability; a book for SQL Server DBA aspirants, System Administrators and Developers, it provides step-by-step information to get you through the installation of various SQL Server High Availability options like Clustering, Replication, Database Mirroring and Log Shipping. Sounds interesting, read more about me at http://www.sql-server-citation.com/p/about-me.htmlSpecialties: IT Project Management, SQL Server High Availability, Performance Tuning I have worked on IT Project Management using latest tools like Six Sigma, PMP and ITIL.Keep in touch with me: Twitter: https://twitter.com/ghemant Facebook: https://www.facebook.com/SQLServerCitation

3 Comments. Leave new

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

Reply

Hi Mohammed,

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

Reply

Hi Aaron,

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

Thank you

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *