I frequently use the SQL Server sp_who and sp_who2 system stored procedures to debug blocking issues with an ERP system that I support. The call will come, "Syteline is hung up again!" and my first reaction will be to execute an sp_who2 to see who is blocking whom.

When looking at the output of sp_who2, I often need to dig a little deeper to determine who the party or process is that responsible for the block.

For this, I typically use the command DBCC INPUTBUFFER(spid). This command shows the first 100 characters or so of the last SQL command executed on a connection and usually will tell me what the blocking process is doing.

While DBCC INPUTBUFFER is useful in general for determining the cause of blocking issues, for determining blocking issues with Syteline it is essential. Syteline uses a three-tier architecture with stored procedures being executed by an apps server, so all connections originate from a single apps server. There's no host name to identify the user who originated the blocking process.

As a times savings, I decided to create my own version of the sp_who procedure that combines the functionality of sp_who and DBCC inputbuffer, and the source of this procedure is shown here below. The p_ExtendedWho procedure includes most of the information and functionality of sp_who and adds an additional [Buffer] column with the contents of the input buffer.

In creating this stored procedure, I made use of fn_get_sql, a system function first made available with SQL Server 2000 sp3. This system function provides similar functionality to DBCC INPUTBUFFER but can be more easily incorporated into a query.

 
CREATE proc dbo.p_ExtendedWHO as

SELECT
	a.spid
	,a.status
	,b.name 'database'
	,a.hostname
	,a.program_name
	,a.cmd
	,a.cpu
	,a.physical_io
	,a.blocked
	,a.dbid
	,convert(sysname, rtrim(a.loginame))
        as loginname

 ,  substring( convert(varchar,a.last_batch,111) ,6  ,5 ) + ' '
  + substring( convert(varchar,a.last_batch,113) ,13 ,8 ) as 'last_batch_char',
	(select [text] from ::fn_get_sql(a.sql_handle)) 'buffer'
	
from 
	master.dbo.sysprocesses a (nolock)
		join master.dbo.sysdatabases b on b.[dbid]=a.[dbid]
order by
	[cpu] desc,
	[physical_io] desc,
	[last_batch_char] desc		

For SQL Server 2000 prior to sp2 a hot fix is available that enables support for the fn_get_sql function. If for whatever reason you are unable to apply this hot fix, then the following version of p_ExtendedWHO also shows buffer contents, but without making use of the fn_get_sql system function.

 
CREATE proc dbo.p_ExtendedWHO as

set nocount on

CREATE TABLE #Inputbuffer(
	EventType NVARCHAR(30) NULL,
	Parameters INT NULL,
	EventInfo NVARCHAR(255) NULL
)

SELECT
	a.spid
	,a.status
	,b.name 'database'
	,a.hostname
	,a.program_name
	,a.cmd
	,a.cpu
	,a.physical_io
	,a.blocked
	,a.dbid
	,convert(sysname, rtrim(a.loginame))
        as loginname

 ,  substring( convert(varchar,a.last_batch,111) ,6  ,5 ) + ' '
  + substring( convert(varchar,a.last_batch,113) ,13 ,8 ) as 'last_batch_char',
	space(1000) 'buffer'
into
	#x 
from 
	master.dbo.sysprocesses a (nolock)
		join master.dbo.sysdatabases b on b.[dbid]=a.[dbid]

declare @SPID bigint,
		@SQL varchar(1000)

declare xcn cursor local fast_forward read_only for
select
	[spid]
from
	#x

open xcn

fetch next from xcn into @SPID

while @@fetch_status=0
begin
	set @SQL='DBCC INPUTBUFFER(' + convert(varchar(25),@SPID) + ')'
	insert into #Inputbuffer
	exec (@SQL)

	update #x set #x.[buffer]=n.[EventInfo]
	from
		#Inputbuffer n
	where
		#x.[spid]=@spid

	fetch next from xcn into @SPID
end
close xcn
deallocate xcn

select 
	a.*
from 
	#x a
order by
	[cpu] desc,
	[physical_io] desc,
	[last_batch_char] desc		

drop table #x
drop table #Inputbuffer

Here is sample output