0

I know there is a DBCC INPUTBUFFER command in SQL Server, which

Displays the last statement sent from a client to an instance of SQL Server.

To me it seems that input buffer IS the SQL a session is running.

SQL Server also has sys.dm_exec_sql_text, which

Returns the text of the SQL batch that is identified by the specified sql_handle.

And sys.dm_exec_input_buffer, which

Returns information about statements submitted to an instance of SQL Server.

I'm kind of confused. They seems very similar and unidentifiable to me.

I also come across an article Understanding the sql_text Action in Extended Events by Jonathan Kehayias. In the article, he states

With this event session created, we can then run a couple of different test scenarios in the environment to show how this action is not the sql_text, but is instead the input_buffer for the event that is being fired

He's very confident that SQL text is not input buffer, which I don't understand the reason. Could anyone help clarify a little bit please? Thanks.

3
  • Execute a stored procedure and compare what the different methods show you Jul 10, 2023 at 8:57
  • I tried. Both DBCC INPUTBUFFER and sys.dm_exec_input_buffer` show ``EXEC dbo.usp_LongRunningTask. The sys.dm_exec_sql_text` shows CREATE PROCEDURE dbo.usp_LongRunningTask AS.... So the input buffer is the executing statement, and sql text is the create procedure statement that defines the procedure? Jul 10, 2023 at 12:43
  • 1
    With the sql text you can use the statement offset to get the exact statement in that proc definition that is currently executing Jul 10, 2023 at 12:45

1 Answer 1

2
  • DBCC INPUTBUFFER
    Displays the command actually sent by the client under the specified session (and particular request). This may be an ad-hoc batch, or a procedure.
  • sys.dm_exec_input_buffer
    Is the newer form of the above, which allows the resultset to be used as part of another query.
  • sys.dm_exec_sql_text
    Gives the SQL text for a particular batch or plan handle.
    You can get this handle in a number of ways, but often you will get one for the currently executing command of a session via sys.dm_exec_requests, which may be nested down the call stack from the original request, such as via a procedure, function or trigger. Because this may be part of a larger SQL batch, you need to use statement_start_offset and statement_end_offset to parse out the exact text of the command being executed.
  • the sql_text Action in Extended Events is a particular issue with XEvents, that the action (data field) called sql_text actually represents the input buffer, not the currently executing command, ie the theoretical result of sys.dm_exec_input_buffer not of sys.dm_exec_sql_text.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.