sys.dm_exec_input_buffer in SQL Server 2016

Hands up: who of you at least once in your DBA career has used the DBCC INPUTBUFFER command to retrieve the last SQL statement that a specific session has submitted to SQL Server? Almost all of you!

We all know that DBCC commands are little bit awkward, because you can’t call them in a T-SQL query, and you also can’t correlate their output with other Dynamic Managment Views/Functions. Just imagine you want to return the last executed SQL statement for every user session …

sys.dm_exec_input_buffer

Things are easier now as of SQL Server 2016, because Microsoft will provides you with the new Dynamic Management Function sys.dm_exec_input_buffer that does the same job as DBCC INPUTBUFFER.

Using sys.dm_exec_input_buffer is quite easy: the DMF expects 2 input parameters – the session and request id of the specific session. The following listing shows you a simple call to that new function.

SELECT * FROM sys.dm_exec_input_buffer(55, 0)
GO

But you can do more sophisticated things, like correlating the information with other DMVs and calling it with the CROSS APPLY operator. Let’s have a look at the following listing.

SELECT
	r.session_id, 
	ib.event_info
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id) ib
WHERE
	s.is_user_process = 1
GO

As you can see here, the query returns all submitted SQL statements for all current executing queries. Easy, isn’t it?

Thanks for your time,

-Klaus