sys.dm_exec_input_buffer in SQL Server 2016

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 …

sys.dm_exec_input_buffer

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,

-Klaus

4 thoughts on “sys.dm_exec_input_buffer in SQL Server 2016”

  1. Nice Article Sir 🙂

    I have a question in my mind.Where does SQL server store the data related to DMV’s.

    Thanks

  2. Klaus Aschenbrenner

    Hello Vimal,

    When you retrieve data from DMVs/DMFs, you get that data from internal data structures of the sqlservr.exe process space.

    Thanks,

    -Klaus

  3. manishkumar1980

    Hi Klaus,

    Superb information.

    1 question struck my mind: what’s the difference between sys.dm_exec_sql_text and this DMF then.

    Internet is not clear on this.

Leave a Reply to Klaus Aschenbrenner Cancel Reply

Your email address will not be published. Required fields are marked *

Enjoy one of my newest Online Trainings:

Design, Deploy, and Optimize SQL Server on VMware

EUR 699 incl. 20% VAT