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.

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