Sys. dm_exec_input_buffer in SQL Server 2016, sys. dmexecrequests

Sys. dm_exec_input_buffer in SQL Server 2016, sys. dmexecrequests

We all know that DBCC commands are a bit embarrassing, because you cannot call them in T-SQL queries, and you cannot associate their output to other DMV/DMF. For example, you want to return the last executed SQL statement for each user session ....

Sys. dm_exec_input_buffer

In SQL Server 2016, things become much simpler, because Microsoft provides you with a new DMFsys. dm_exec_input_buffer, which does the same work as DBCC INPUTBUFFER.

Using sys. dm_exec_input_buffer is very simple: This DMF requires two input parameters: the session and the request id of the specified session. The following code shows a simple example of calling a new function.

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

But you can do more complex things, such as associating information with other DMV using the cross apply operator. Let's take a look at the following code.

SELECTr. session_id, ib. event_infoFROM sys. dm_exec_requests rJOIN sys. dm_exec_sessions s ON s. session_id = r. session_idCROSS APPLY sys. dm_exec_input_buffer (r. session_id, r. request_id) ibWHEREs. is_user_process = 1GO

As you can see here, this query returns all submitted SQL statements for all currently executed queries. It's easy, isn't it?