New DMF for retrieving input buffer in SQL Server

Published Mar 23 2019 02:27 PM 156 Views
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
%3CLINGO-SUB%20id%3D%22lingo-sub-384693%22%20slang%3D%22en-US%22%3ENew%20DMF%20for%20retrieving%20input%20buffer%20in%20SQL%20Server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-384693%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Mar%2015%2C%202016%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CBLOCKQUOTE%3EA%20new%20DMF%20for%20retrieving%20the%20input%20buffer%20for%20a%20session%2Frequest%20(%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fmt652096.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20sys.dm_exec_input_buffer%20%3C%2FA%3E%20)%20is%20now%20available%20in%20%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fevalcenter%2Fevaluate-sql-server-2016%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20SQL%20Server%202016%20RC0%20%3C%2FA%3E%20.%20This%20is%20functionally%20equivalent%20to%20DBCC%20INPUTBUFFER.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20However%2C%20this%20has%20a%20few%20advantages%20over%20DBCC%20INPUTBUFFER.%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EDirectly%20returns%20a%20rowset.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ECan%20be%20conveniently%20used%20with%20%3CSTRONG%3E%20sys.dm_exec_sessions%20%3C%2FSTRONG%3E%20or%20%3CSTRONG%3E%20sys.dm_exec_requests%20%3C%2FSTRONG%3E%20by%20doing%20a%20CROSS%20APPLY.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ECan%20use%20a%20simple%20query%20to%20retrieve%20input%20buffer%20of%20multiple%20sessions%20without%20the%20need%20for%20a%20script%20and%20temp%20table.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CBLOCKQUOTE%3EFor%20example%3A%20%3CBR%20%2F%3E%3CDIV%20id%3D%22codeSnippetWrapper%22%3E%3CBR%20%2F%3E%3CDIV%20id%3D%22codeSnippet%22%3E%3CBR%20%2F%3E%3CDIV%20id%3D%22codeSnippetWrapper%22%3E%3CBR%20%2F%3E%3CDIV%20id%3D%22codeSnippet%22%3E%3CBR%20%2F%3E%20SELECT%20es.session_id%2C%20ib.event_info%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20FROM%20sys.dm_exec_sessions%20AS%20es%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CROSS%20APPLY%20sys.dm_exec_input_buffer(es.session_id%2C%20NULL)%20AS%20ib%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20WHERE%20es.session_id%20%26gt%3B%2050%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%0A%20%20%20%20%20%20%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3EResults%3A%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3Esession_id%20event_info%20%3CBR%20%2F%3E%20----------%20---------------------------------------------------------------------------%20%3CBR%20%2F%3E%2051%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20select%20value_in_use%20from%20sys.configurations%20where%20configuration_id%20%3D%2016384%20%3CBR%20%2F%3E%2052%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20SELECT%20es.session_id%2C%20ib.event_info%20FROM%20sys.dm_exec_sessions%20AS%20es%20CROSS%20APPLY%20sys.dm_exec_input_buffer(es.session_id%2C%20NULL)%20AS%20ib%20WHERE%20es.session_id%20%26gt%3B%2050%3B%20%3CBR%20%2F%3E%2053%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20(%40source%20nvarchar(256)%2C%40sourceopt%20int)SELECT%20type%2C%20data%20FROM%20sys.fn_MSxe_read_event_stream%20(%40source%2C%20%40sourceopt)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3C%2FDIV%3E%0A%20%20%20%20%3C%2FDIV%3E%0A%20%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%20%3CEM%3EAjay%20Jagannathan%20(%20%3C%2FEM%3E%20%3CEM%3E%20%3CA%20href%3D%22https%3A%2F%2Ftwitter.com%2Fajaymsft%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20%3CB%3E%20%40ajayMSFT%20%3C%2FB%3E%20%3C%2FA%3E%20)%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20Principal%20Program%20Manager%3C%2FEM%3E%3C%2FBLOCKQUOTE%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-384693%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Mar%2015%2C%202016%20A%20new%20DMF%20for%20retrieving%20the%20input%20buffer%20for%20a%20session%2Frequest%20(sys.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-384693%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerTiger%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 02:27 PM
Updated by: