| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

Retrieving the input buffer in SQL Server 2016 using the new Dynamic Management Function - sys.dm_exec_input_buffer
by BF (Principal Consultant; Architecture; Engineering)

Starting in SQL Server 2016 we now have the ability to retrieve the Input Buffer in SQL Server Engine using a new DMF(Dynamic
Management Function) called sys.dm_exec_input_buffer. This new DMF has some advantages over DBCC INPUTBUFFER such as
returning a rowset, can be used with sys.dm_exec_sessions or sys.dm_exec_requests by doing CROSS APPLY and it can retrieve
multiple session's input buffer with a simple query.


This DMF expects 2 input parameters – the session & request id of the specific session. i.e. sys.dm_exec_input_buffer(session_id , request_id)

r.session_id, ib.event_info, s.status
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
s.is_user_process = 1
and s.session_id <> @@SPID
and s.status in ('Runnable', 'Running', 'Suspended');

With this new DMF, now we do not have to use the older DBCC INPUTBUFFER command which was not possible to correlate with other Dynamic
Management Views/Functions. Now we can execute sys.dm_exec_input_buffer and return the last executed SQL statement for every user session
for all current executing queries!!!


As per MSDN:

"On SQL Server, if the user has VIEW SERVER STATE permission , the user will see all executing sessions on
the instance of SQL Server; otherwise, the user will see only the current session.

On SQL Database, if the user is the database owner, the user will see all executing sessions on
the SQL Database; otherwise, the user will see only the current session."


sys.dm_exec_input_buffer (Transact-SQL)