New DMF for retrieving input buffer in SQL Server

Published Mar 23 2019 02:27 PM 176 Views
Microsoft
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
Version history
Last update:
‎Mar 23 2019 02:27 PM
Updated by: