info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



Retrieving the input buffer in SQL Server 2016 using the new Dynamic Management Function - sys.dm_exec_input_buffer
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-12-06









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.



T-SQL:

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)

SELECT
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
WHERE
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!!!



Permissions:

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."



Resources:

sys.dm_exec_input_buffer (Transact-SQL)