Problem
SQL Server 2016 introduced a new dynamic management function that can be used to display the last T-SQL statement submitted by a client to SQL Server. This new DMF is considered as an extended and enhanced version of the DBCC INPUTBUFFER command that is widely used by Database Administrators (DBAs) to track executed queries. In this tip, we will discuss the DBCC INPUTBUFFER command and the new function sys.dm_exec_input_buffer and compare these two commands.
Solution
It is very rare to find a DBA who is not familiar with the DBCC INPUTBUFFER command. The DBCC INPUTBUFFER command is mainly used to track and display the last submitted T-SQL query to a SQL Server instance from a particular session. You need to provide the session ID that is associated with the active connection and the exact request ID that you need to search for within the provided session. It is also commonly used to track blocking and blocked queries as a second step after getting the blocking and blocked sessions IDs using sp_who, sp_locks or sp_who2.
The DBCC INPUTBUFFER command has one limitation, if you want to retrieve the last T-SQL query submitted from more than one session, you need to execute the DBCC INPUTBUFFER command separately for each session ID. This is because you cannot correlate the input value for the DBCC INPUTBUFFER command by using values from another dynamic management view or function.
SQL Server 2016 keeps surprising us and with its new features and enhancements comes a new dynamic management function sys.dm_exec_input_buffer DMF.
This is an extended version of the DBCC INPUTBUFFER command that is also used to retrieve the last executed query for a specific session. The improvement in the sys.dm_exec_input_buffer DMF lies in the ability to join to other DMVs and DMFs to retrieve the last T-SQL queries executed by two or more sessions or all user sessions at the same time.
It’s demo time!
We have three user sessions with assigned IDs 53, 54 and 55 respectively, as shown below. We will show different examples of retrieving information about these sessions.
Using sp_who2
The first step that can be used to track submitted queries is using sp_who2. This system stored procedure can be used to retrieve useful information about a specific session, by providing the SPID for that session to the sp_who2 procedure or you can retrieve information about all sessions without providing a spid value.
The sp_who2 procedure is very useful to get information about sessions such as the session status, the host name, the login name and the SQL resource consumption by each session. Unfortunately, sp_who2 still cannot be used by itself to track the full query information, as it provides no information about the T-SQL statement and we can’t join to other system DMVs and DMFs.
When executing the sp_who2 on a separate session, the below information will be shown about the previously executed queries, without showing the T-SQL command of the executed queries:
Using DBCC INPUTBUFFER
In order to retrieve the T-SQL command of a particular session, the DBCC INPUTBUFFER command can be easily used to show that, by passing the session ID to the DBCC command. As discussed previously, in order to show the T-SQL command for more than one session, you need to run the DBCC INPUTBUFFER command for each session. In our case we are retrieving information for three sessions as shown below:
DBCC INPUTBUFFER (53)
DBCC INPUTBUFFER (54)
DBCC INPUTBUFFER (55)
A single record will be retrieved from each run of the DBCC INPUTBUFFER command for the provided session ID, including the EventType that could be an RPC Event, Language Event or No Event in the case of no detected event.
The Parameters output could be 0 for text output and 1-n for the parameters. The EventInfo shows the procedure name for RPC EventType and the first 4000 characters of the query text in the case of Language EventType. The result retrieved from executing the previous DBCC INPUTBUFFER commands will be like below:
Using sys.dm_exec_input_buffer
What about using the newly introduced the sys.dm_exec_input_buffer DMF?
If we use the sys.dm_exec_input_buffer DMF without joining with any other DMV or DMF, you need to provide the session ID for each session and again execute it three times to get the T-SQL command information for the three sessions as shown below:
SELECT * FROM sys.dm_exec_input_buffer(53, NULL)
GO
SELECT * FROM sys.dm_exec_input_buffer(54, NULL)
GO
SELECT * FROM sys.dm_exec_input_buffer(55, NULL)
GO
The same information retrieved previously from the DBCC INPUTBUFFER command will be also returned from executing the sys.dm_exec_input_buffer DMF, as shown in the result below:
We still cannot see any enhancements for the sys.dm_exec_input_buffer over the DBCC INPUTBUFFER command, as both commands returned the same information about the last submitted query on a specific session.
The result of the new sys.dm_exec_input_buffer DMF can be easily joined with other DMVs and DMFs using the unique session ID number, to get extra information about the current session or getting information about all connected sessions to for the SQL Server instance using a single query.
For example, the sys.dm_exec_input_buffer DMF can be used with the sys.dm_exec_sessions server-scope DMV, that returns one row per each active user session or internal tasks. You can get valuable information about the connected sessions from the sys.dm_exec_sessions DMV such as the program name, the login user and login time.
The below query combines between the sys.dm_exec_input_buffer DMF and the sys.dm_exec_sessions DMV using CROSS APPLY join operator to get the last T-SQL command of all connected user sessions, by passing the session ID retrieved from the sys.dm_exec_sessions DMV to the sys.dm_exec_input_buffer DMF:
SELECT Ses.session_id,InBuf.event_info
FROM sys.dm_exec_sessions Ses
CROSS APPLY sys.dm_exec_input_buffer(Ses.session_id,0) InBuf
WHERE Ses.session_id>50;
Using the previous query, the last executed T-SQL command for all connected user sessions are retrieved at once as shown in the result below:
You can also combine data between the sys.dm_exec_input_buffer DMF and the sys.dm_exec_requests DMV, that returns one row of information about each request that is currently executing within the SQL Server instance. This can be easily performed using the CROSS APPLY join operator as shown in the T-SQL script below:
SELECT
Req.session_id ,InBuf.event_info
FROM sys.dm_exec_requests AS Req
JOIN sys.dm_exec_sessions AS Ses
ON Ses.session_id = Req.session_id
CROSS APPLY sys.dm_exec_input_buffer(Req.session_id, Req.request_id) AS InBuf
WHERE
Ses.session_id>50 and Ses.is_user_process = 1
GO
Again, the last T-SQL command executed for all currently running user sessions are retrieved at once as shown in the result below:
Next Steps
- Read more about Dynamic Management Views
- Check Different techniques to identify blocking in SQL Server
- Check out these too:
