Sys.dm_exec_input_buffer in SQL Server 2016

In your DBA profession, who have you ever worked with the DBCC inputbuffer command to get the last SQL statement that has been committed to a specific session of SQL Server? Raise your hand, please! Everyone has used it!

We all know that DBCC commands are a little awkward because you can't call them in T-SQL queries, and you can't correlate their output to other DMV/DMF. For example, you want to return the last SQL statement executed for each user session ....

Sys.dm_exec_input_buffer

In SQL Server 2016, things are much simpler because Microsoft provides you with a new DMFSys.dm_exec_input_buffer, which does the same job as DBCC inputbuffer .

Using Sys.dm_exec_input_buffer is very simple: this DMF requires 2 input parameters--the session and the request ID of the specified session. The following code shows a simple example of invoking a new function.

SELECT *  from Sys.dm_exec_input_buffer (0)GO

But you can do more complex things, like associating information with other DMV with the cross APPLY operator. Let's look at the following code.

SELECT     r.session_id,     ib.event_infofrom  sys.dm_exec_requests rJOINon  =  r.session_id cross APPLY sys.dm_exec_input_buffer (r.session_id, R.request_ ID) IBWHERE    =1GO

As you can see here, this query returns all the committed SQL statements for all currently executing queries. It's simple, isn't it?

Thanks for your attention!

original link

https://www.sqlpassion.at/archive/2016/04/18/sys-dm_exec_input_buffer-in-sql-server-2016/

Sys.dm_exec_input_buffer in SQL Server 2016