CODEWITHSUNDEEP
sqlsql-servert-sql
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 34024

Difference between dm_exec_sql_text vs dm_exec_input_buffer

As per Microsoft docs, dm_exec_sql_text returns the text of the SQL batch that is identified by the specified sql_handle while dm_exec_input_buffer returns information about statements submitted to an instance of SQL Server.

I used DBCC INPUTBUFFER for getting the last executed SQL. But it seems both dm_exec_sql_text and dm_exec_input_buffer are replacement DBCC INPUTBUFFER. If this is the case then what is the main difference between them? When should we use one of them?

Upvotes: 1

Views: 321

Answers (1)

Charlieface
Charlieface

Reputation: 72020

sys.dm_exec_input_buffer represents only the batch or RPC call that began the request, in other words the command initiated by the client directly, not any later procedures or triggers called. As noted in the documentation, you can only pass a session_id and request_id. This is the equivalent of DBCC INPUTBUFFER as you have noted.

Meanwhile, sys.dm_exec_sql_text can be passed an sql_handle or a plan_handle, which can be got from various places, not least from sys.dm_exec_requests, and retrieves the currently executing batch scope (ad-hoc or procedure or trigger), which means that you can get the text of the currently executing statement even if it's inside a procedure, not just the initial batch.

Upvotes: 2

Related Questions