New DMF for retrieving input buffer in SQL Server

[Ajay_MSFT]

First published on MSDN on Mar 15, 2016

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