New DMF for retrieving input buffer in SQL Server


A new DMF for retrieving the input buffer for a session/request (sys.dm_exec_input_buffer) is now available in SQL Server 2016 RC0. This is functionally equivalent to DBCC INPUTBUFFER.

However, this has a few advantages over DBCC INPUTBUFFER.

  • Directly returns a rowset.
  • Can be conveniently used with sys.dm_exec_sessions or sys.dm_exec_requests by doing a CROSS APPLY.
  • Can use a simple query to retrieve input buffer of multiple sessions without the need for a script and temp table.

For example:

SELECT es.session_id, ib.event_info
FROM sys.dm_exec_sessions AS es
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib
WHERE es.session_id > 50
Results:
session_id event_info
---------- ---------------------------------------------------------------------------
51         select value_in_use from sys.configurations where configuration_id = 16384
52         SELECT es.session_id, ib.event_info FROM sys.dm_exec_sessions AS es CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib WHERE es.session_id > 50;
53         (@source nvarchar(256),@sourceopt int)SELECT type, data FROM sys.fn_MSxe_read_event_stream (@source, @sourceopt)

Ajay Jagannathan (@ajayMSFT)

Principal Program Manager

Comments (2)

  1. Kevin Boles says:

    Will it show all the text or will it truncate like dbcc inputbuffer does?

  2. Ajay.MSFT says:

    It will show all the text as we changed the datatype to nvarchar(max).

Skip to main content