SQL SERVER – Alternative to DBCC INPUTBUFFER – sys.dm_exec_input_buffer

DBCC INPUTBUFFER has been one of the most popular commands to display the last statement sent from a client to an instance of Microsoft SQL Server. We all have been using it for quite a while. However, this DBCC command showed very little information and DBA always wanted to see more details. In SQL Server 2016 we have a new Dynamic Management Function (DMV) sys.dm_exec_input_buffer which provides many additional details as well.

Earlier, everytime when I used to run DBCC INPUTBUFFER, I always felt that I should have some additional details along with this one like how many rows the queries are processed or how much resources they are consuming. In the new DMF, we have quite a lot of useful information along with session IDs and query running.

Another limitation of the DBCC INPUTBUFFER was that it had to be passed in session_id and there was no way we can join that with current sessions DMV to run it for multiple session ids. This limitation also goes away as the new function sys.dm_exec_input_buffer now can be joined with sys.dm_exec_sessions.

Let us run following query and observe its output.

SELECT es.session_id, ib.event_info, status,
		cpu_time, memory_usage, logical_reads, writes, row_count
		total_elapsed_time, login_time, last_request_start_time, last_request_end_time
		host_name, program_name, login_name, open_transaction_count
FROM sys.dm_exec_sessions AS es
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib
WHERE es.session_id > 50

Here is the result set of the above query.

SQL SERVER - Alternative to DBCC INPUTBUFFER - sys.dm_exec_input_buffer dm_exec_input_buffer-800x193

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – How to Rename Extention of MDF File? – A Simple Tutorial
Next Post
SQL SERVER – Different Methods to Know COMPATIBILITY LEVEL of a Database

Related Posts

4 Comments. Leave new

  • Invalid object name ‘sys.dm_exec_input_buffer’.

    Reply
  • Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.dm_exec_input_buffer’.

    Reply
  • Make sure you are running 2016, we are running 2014 and this command does not work. Use SELECT @@Version

    Reply
  • chris dickey
    March 9, 2019 7:10 am

    Get the result but also a permission denied error. SQL Server 2017 CU13

    select event_info FROM sys.dm_exec_input_buffer(@@spid, NULL)
    seems like a bug since the documentation says you should be able to view the info for the current spid. Should not get an error. I found out the hard way. Put this in a proc to retrieve event_info. The proc ran and completed. The web client app got the error message.

    Reply

Leave a Reply

Menu