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)
-- 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)
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
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 )