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 …
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,