This article gives an overview of the DBCC INPUTBUFFER and its replacement sys.dm_exec_input_buffer system dynamic management function .
Introduction
Suppose you connect to a SQL instance and identify current running sessions and their query texts. Usually, DBAs use a system stored procedure called sp_who2 to list all sessions along with little diagnostic information such as CPU, memory, etc. It is rare to find any DBA that does not know the sp_who2 command. We get a row corresponding to each SPID. Here SPID belongs to Server Process ID.
Here is the output of the sp_who2 command in an idle instance:
SPID values of 1 to 50 belong to system processes such as log writer, lock monitor, checkpoint, and resource monitor.
Status: It gives the status of the particular SPID such as Sleeping, Background, Running and Runnable:
- CPU time: It is the total time of CPU time for that particular process
- Disk IO: It is the total disk read/write time for that particular process
- Last batch: It gives the timestamp of the last statement from the process
- DB Name: The SPID is executing a query in the database name
- Program Name: It shows the Program name such as SSMS, SQL Agent from where the connection is coming to SQL Server
Now let’s execute a query in SQL Server instance to fetch records from a table. In the screenshot, we can see the query is executing in SPID 62:
Let’s say we do not know the query and its source. We can use the sp_who2 procedure to find out what is running under this SPID.
We can filter the records for the SPID 62 using the following query:
|
1 |
sp_who2 62 |
We can see login, database and program name for this SPID, but it does not show the query running under this SPID:
We use another DBCC INPUTBUFFER command for getting the last statement executed in a particular SPID. We need to pass the SPID number in the argument:
|
1 |
DBCC INPUTBUFFER(62) |
Is it a good way to identify query text for the SPID? Let’s explore a few drawbacks of using sp_who2 and DBCC INPUTBUFFER command:
- We cannot use this command to retrieve query text for all sessions
- For multiple sessions, we need to run the DBCC INPUBUFFER command for each session. For example, if we want to check the query text for 60, 61, 62 SPID’s, we need to run the command as shown below
We get individual output for each command along with event type and event info (query). By default, it shows only 4000 characters of the query for the language event type:
Permissions required for DBCC INPUTBUFFER command
We need one of the following permissions for executing this command:
- A user must be a member of the sysadmin fixed server role
- A user must have VIEW SERVER STATE permission
Alternatives of DBCC INPUTBUFFER command
SQL Server provides useful dynamic management views and functions. We usually use the following acronyms for these views and functions:
- DMV: Dynamic Management View
- DMF: Dynamic Management Function
These DMV and DMF provide server state information and useful in monitoring SQL Server instance, performance issues, internal query behavior, waits, etc.
SQL Server 2014 SP2 introduced a new DMF sys.dm_exec_input_buffer as a replacement of the DBCC INPUTBUFFER command. We can use this DMF to retrieve the last query executed similar to the DBCC command. The benefit of it is that you can retrieve information for two or more sessions at the same time. It also allows you to join it with other DMV/DMF for fetching useful information.
Syntax of sys.dm_exec_input_buffer
|
1 |
sys.dm_exec_input_buffer ( session_id , request_id ) |
It requires two arguments:
- Session_ID: We use the SPID for which we want to retrieve the information. We use the same SPID
in DBCC INPUTBUFFER. We can use other DMV’s for fetching the SPID automatically for all sessions
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.dm_exec_connections
- Request_ID: It is the unique ID of the session. We can retrieve it from sys.dm_exec_requests DMV. It also allows NULL value for this argument
Previously we use the DBCC command for retrieving query text for SPID 60, 61, and 62. Let’s use the sys.dm_exec_input_buffer command for retrieving the same information:
|
1 2 3 4 5 6 7 8 9 |
SELECT * FROM sys.dm_exec_input_buffer(60, NULL); GO SELECT * FROM sys.dm_exec_input_buffer(61, NULL); GO SELECT * FROM sys.dm_exec_input_buffer(62, NULL); GO |
It returns a similar output using the DMF as well in comparison with the DBCC INPUTBUFFER command:
Why should we use this sys.dm_exec_input_buffer DMF if it is returning the similar output as of DBCC INPUTBUFFR command?
The benefits of using the DMF are:
- You can join it with other DMV\DMF for providing input for session-id automatically
- We can retrieve extra information with the join of multiple DMV, DMF’s
- You can filter results for specific session id or exclude system session-id’s from the output
The following query combines sys.dm_exec_input_buffer DMF and sys.dm_exec_sessions DMV. We use the CROSS APPLY join operator between these for retrieving T-SQL statements of all connected user sessions (SPID>50).
You can notice that DMF takes the value of the argument session_id from the session id of sys.dm_exec_sessions DMV. It does not require you to specify session_id manually which is a drawback of the DBCC command.
In the output, you can see additional columns such as CPU_time, logical_reads, writes, total_elapsed time, program name in the same window. You do not need to execute separate queries and combine outputs. We also get an output of multiple session id’s in a single output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT dmes.session_id, ib.event_info, STATUS, cpu_time, memory_usage, logical_reads, writes, row_count total_elapsed_time, last_request_start_time, last_request_end_time host_name, program_name, login_name FROM sys.dm_exec_sessions AS dmes CROSS APPLY sys.dm_exec_input_buffer(dmes.session_id, NULL) AS ib WHERE dmes.session_id > 50 |
Similarly, the following query combines DMF sys.dm_exec_requsts with the DMV sys.dm_exec_requests DMV using the CROSS APPLY join operator:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT Req.session_id, InBuf.event_info, ses.host_name, ses.client_interface_name, ses.open_transaction_count,ses.is_user_process 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 |
Permissions required for sys.dm_exec_input_buffer DMF
- It requires a VIEW SERVER STATE permission to view all executing sessions on the instance. User can see only the current session without this permission
- If a user is a database owner, it sees all executing sessions on the database. User can see only the current session without this permission
Conclusion
It is always better to use the latest query, commands to enhance productivity and flexibility. We can still use the old way DBCC INPUTBUFFER, but I would recommend using sys.dm_exec_input_buffer dynamic management function instead. If you have not tried it before, use it and become familiar with it. It will give more useful information and saves time for you in executing multiple statements and combine results.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023