Rajendra Gupta
The output of DMF sys.dm_exec_input_buffer

Overview of DBCC INPUTBUFFER and sys.dm_exec_input_buffer DMF

November 7, 2019 by

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:

output of the sp_who2

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:

Session ID

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:

We can see login, database and program name for this SPID, but it does not show the query running under this SPID:

Filter results of sp_who2 command

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:

DBCC INPUTBUFFER

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:

Output for multiple sessions

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

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:

It returns a similar output using the DMF as well in comparison with the DBCC INPUTBUFFER command:

The output of DMF sys.dm_exec_input_buffer

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:

the output of sys.dm_exec_input_buffer to filter results

Similarly, the following query combines DMF sys.dm_exec_requsts with the DMV sys.dm_exec_requests DMV using the CROSS APPLY join operator:

CROSS APPLY with sys.dm_exec_requests

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. Monitoring SQL Server with Dynamic Management Objects – Requests
  2. KILL SPID command in SQL Server
  3. Monitoring SQL Server with Dynamic Management Objects – Sessions and connections
  4. SQL Server 2019 – New DMF sys.dm_db_page_info
  5. Resolve and Troubleshoot SQL Blocking chains with root sessions
168 Views