sys.dm_exec_input_buffer in SQL Server 2016

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Hands up: who of you at least once in your DBA career has used the DBCC INPUTBUFFER command to retrieve the last SQL statement that a specific session has submitted to SQL Server? Almost all of you!

We all know that DBCC commands are little bit awkward, because you can’t call them in a T-SQL query, and you also can’t correlate their output with other Dynamic Managment Views/Functions. Just imagine you want to return the last executed SQL statement for every user session …

sys.dm_exec_input_buffer

Things are easier now as of SQL Server 2016, because Microsoft will provides you with the new Dynamic Management Function sys.dm_exec_input_buffer that does the same job as DBCC INPUTBUFFER.

Using sys.dm_exec_input_buffer is quite easy: the DMF expects 2 input parameters – the session and request id of the specific session. The following listing shows you a simple call to that new function.

But you can do more sophisticated things, like correlating the information with other DMVs and calling it with the CROSS APPLY operator. Let’s have a look at the following listing.

As you can see here, the query returns all submitted SQL statements for all current executing queries. Easy, isn’t it?

Thanks for your time,

-Klaus

4 Comments

  • Nice Article Sir 🙂

    I have a question in my mind.Where does SQL server store the data related to DMV’s.

    Thanks

  • Klaus Aschenbrenner

    Hello Vimal,

    When you retrieve data from DMVs/DMFs, you get that data from internal data structures of the sqlservr.exe process space.

    Thanks,

    -Klaus

  • manishkumar1980

    Hi Klaus,

    Superb information.

    1 question struck my mind: what’s the difference between sys.dm_exec_sql_text and this DMF then.

    Internet is not clear on this.

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top