Relational Database Systems

Beyond DBCC InputBuffer

Recently, I was interviewing for DBA position and asked question "There is blocking on server, how will you find complete sql statement that is causing blocking?". Candidate answered question similar what most of DBA answers. His step by step answer was something like this:

Step 1: Run sp_who2 active to find SPID that is causing blocking

Step 2: Run dbcc inputbuffer(<spid>) to see what statement is currently executing

My Next Question was, "If Stored Procedure is calling other two stored procedure inside then how will you find which one is currently running?"

He could not answer this question. Similar situation happens with multiple candidate who came for interview then I realized that mostly DBA have not gone beyond DBCC Inputbuffer. Intention of this article is to take DBA beyond DBCC Inputbuffer who have not experimented or used before.

You got it right, I am here referring fn_get_sql that returns the text of the SQL Statement for the specified SQL Handle. DBCC InputBuffer has certain limitation like:

  • It can not return more than 255 character
  • If there are two stored procedure where 1st SP calls 2nd SP and currently sp2 is running then dbcc inputbuffer will return information about 1st SP that is also just procedure name with parameter upto 255 characters.

Whereas if you use fn_get_sql it can return more than 255 characters and also it will be highest current nesting level of a stored procedures along with complete text of stored procedures.

Lets have a looks at its usage:

For SQL Server 2000 :

declare @sql_handle varbinary(64)
select @sql_handle = sql_handle from master..sysprocesses where spid=148

select dbid, objectid, text from ::fn_get_sql(@sql_handle)

For SQL Server 2005 and 2008:

declare @sql_handle varbinary(64)
select @sql_handle = sql_handle from sys.dm_exec_requests where session_id=126 and request_id = 0

select dbid, objectid, text from sys.fn_get_sql(@sql_handle)

Above Query will return result similar as shown below:

You may change query to get database name using db_name(<dbid>) and object_name(<objectid>) function.

Please Note: Query that I provided for SQL Server 2000, can be used on SQL Server 2005 and SQL Server 2008 but it is not recommended because it is better to use query/function that will be supported in future version of SQL Server.

Now lets have a look at few important facts about fn_get_sql:

  1. fn_get_sql will not return value if handle passed to it no longer exists in cache.
  2. Text column of the result set is filtered for text that may contain password
  3. Few Transact-SQL Statements like bulk copy and statements with string literals that are larger than 8 KB, are not being cached by SQL Server Database engine and hence those statements can not be retrieved by using fn_get_sql.

Conclusion:

DBCC Inputbuffer is great to find process event information but has certain limitation and fn_get_sql is solution to go beyond those limitations.

1 comment on "Beyond DBCC InputBuffer"

  1. Anvesh
    Reply

    Nice Article !
    This is my pleasure to read your article.
    Really this will help to people of SQL Server Community. 

    I have also prepared one article about, Get last executed statement of lead blocker query using SQL Server DBCC Inputbuffer
    You can also visit my article, your comments and reviews are most welcome. 

    http://www.dbrnd.com/2017/01/sql-server-dbcc-inputbuffer-to-find-the-last-statement-executed-by-a-spid-bloc-deadlock/

Leave a Reply

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

To Top