SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

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

-- Variable that will pass on the culprit SPID

-- value for culprit SPID
SET @SPID = 52

-- this will give you the SQLHandle for the culprit SPID

-- this statement will give you the SQL Statement for culprit SPID
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}
BTW, if you happened to come across something relating but on SQL Server 2005 or SQL Server 2008 and greater I have a reference script for you

Erland Sommarskog, SQL Server MVP has written aba_lockinfo and a script a.k.a. Custom Blocker Report from Aaron_Bertrand.
--Hemantgiri S. Goswami (http://www.sql-server-citation.com )

SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

Hemantgiri S. Goswami is working as a Consultant at Surat, INDIA. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com. He is an Author of the book SQL Server 2008 High Availability.


Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...