Software Development Blog

Background

Most people who use SQL Server regularly, know about the wonderful DBCC InputBuffer command. If you’re not a SQL aficionado, you can easily find out what other connections “are doing” when one of them starts causing problems. Checking the input buffer - how commands are received by the server - returns the SQL code that was most recently sent from that connection.

For example, if spid ( Server Process Identifier ) #72 is blocking other transactions, or chewing up an alarming amount of disc I/O ( especially if it’s an ad hoc query ), you can find out what it’s up to by calling:

DBCC InputBuffer (72)

Which returns something like this:

Beyond the Basics

This is a good start, but has some issues. Most importantly, the results simply aren’t available to T-SQL code. You can write an application to call DBCC externally - the Activity Monitor does this, giving you the SQL involved when you double click a process - but this limits the utility. For example, if you run sp_who2 and find a dozen questionable spids, you will have to check them individually.

On the other hand, a user-defined function ( UDF ) opens many doors. Logging comes immediately to mind, and will be the subject of a future post. A more straight-forward use for a SQL function is to simply ask what’s running on the server:

Select
	dbo.InputBuffer(spid) As RunningCode,
	*
From
	sys.sysProcesses
Where
	blocked != 0
	Or spid In (Select blocked From sys.sysProcesses)

Instead of simply getting a dump of the spid/kpid values involved, the CPU and I/O numbers, etc, this query returns at-a-glance what each connection is doing. The where clause in the query above will return only connections that are involved in a blocking chain - which is useful when debugging concurrency issues - but the concept applies no matter what you’re doing. You could rewrite the query “… Where dbo.InputBuffer(spid) Like ‘%My_Table_Name%‘ …” to see which transactions are trying to use a table, and what they’re doing with it.

The Code

The T-SQL batch below creates a function named dbo.InputBuffer to the database. This could of course be deployed to any schema; dbo is used for simplicy, as it exists by default in all databases.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

If Exists (Select * From sys.objects Where object_id = object_id(N'dbo.InputBuffer') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	Drop Function dbo.InputBuffer
GO

Create Function dbo.InputBuffer(@spid int) Returns varChar(max) As Begin
	Declare @code varChar(max)

	Select
		@code = SubString(
			st.text,
			(er.statement_start_offset / 2)+1,
			((Case er.statement_end_offset When -1 Then DataLength(st.text) Else er.statement_end_offset End - er.statement_start_offset) / 2) + 1
		)
	From
		sys.dm_exec_requests As er Cross Apply
		sys.dm_exec_sql_text(er.sql_handle) As st
	Where
		er.session_id = @spid;

	Return @code
End

Caveat Emptor (sys.dm_exec_requests)

There is one important difference between the code presented here, and DBCC. Checking the input buffer through the database command console gives you the last command received on any open connection. On the other hand, sys.dm_exec_requests DMV only tracks queries that are currently executing. This is a subtle distinction, but can be a very important one.

If somebody opens a query window in SSMS or Query Analyzer, runs an update query, and then doesn’t close the window once the transaction commits, DBCC will return the update query, but the function I’m presenting will not.

Alternate Version

Often, it’s useful to see NULL returned as an indicator that a given connection is dormant, but, sometimes what’s needed is the behavior from DBCC. In those cases, the code below is a better fit.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

If Exists (Select * From sys.objects Where object_id = object_id(N'dbo.InputBuffer2') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	Drop Function dbo.InputBuffer2
GO

Create Function dbo.InputBuffer2(@spid int) Returns varChar(max) As Begin
	Declare @code varChar(max)

	Select
		@code = st.text
	From
		sys.dm_exec_connections As ec Cross Apply
		sys.dm_exec_sql_text(ec.most_recent_sql_handle) As st
	Where
		ec.session_id = @spid;

	Return @code
End

You might instead want to use a nullable input parameter (@spid int, @exactBehavior bit = 1), or simply to deploy only whichever version of the code does what you want.

In the next episode, we’ll look at how to get the query plan for a transaction running against the server.