When working with SQL Server, it is important for database administrators to have a clear understanding of the queries being executed by different sessions. Traditionally, the sp_who2 stored procedure has been used to retrieve information about running sessions, including CPU time, disk IO, and program name. However, this approach has some limitations, such as the inability to retrieve query text for all sessions and the need to run the DBCC INPUTBUFFER command for each session individually.

In SQL Server 2014 SP2, a new dynamic management function (DMF) called sys.dm_exec_input_buffer was introduced as a replacement for the DBCC INPUTBUFFER command. This DMF provides a more efficient and flexible way to retrieve the last query executed in a specific session. Unlike the DBCC command, sys.dm_exec_input_buffer allows you to retrieve information for multiple sessions at the same time and join it with other DMVs and DMFs for additional insights.

The syntax of sys.dm_exec_input_buffer is as follows:

sys.dm_exec_input_buffer(session_id, request_id)

The session_id parameter represents the SPID for which you want to retrieve information, while the request_id parameter is the unique ID of the session. You can retrieve the request_id from the sys.dm_exec_requests DMV.

Here is an example of using sys.dm_exec_input_buffer to retrieve query text for multiple sessions:

SELECT * FROM sys.dm_exec_input_buffer(60, NULL);
SELECT * FROM sys.dm_exec_input_buffer(61, NULL);
SELECT * FROM sys.dm_exec_input_buffer(62, NULL);

By using the CROSS APPLY join operator, you can combine sys.dm_exec_input_buffer with other DMVs and DMFs to retrieve additional information about the sessions. For example, the following query combines sys.dm_exec_input_buffer with sys.dm_exec_sessions to retrieve T-SQL statements of all connected user sessions (SPID > 50):

SELECT dmes.session_id, ib.event_info, dmes.STATUS, dmes.cpu_time, dmes.memory_usage, dmes.logical_reads, dmes.writes, dmes.row_count, dmes.total_elapsed_time, dmes.last_request_start_time, dmes.last_request_end_time, dmes.host_name, dmes.program_name, dmes.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, you can combine sys.dm_exec_input_buffer with sys.dm_exec_requests to retrieve information about executing sessions:

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;

It is important to note that the sys.dm_exec_input_buffer DMF requires the VIEW SERVER STATE permission to view all executing sessions on the instance. Without this permission, users can only see the current session. Database owners, on the other hand, can see all executing sessions on the database.

In conclusion, while the DBCC INPUTBUFFER command can still be used, it is recommended to utilize the sys.dm_exec_input_buffer dynamic management function instead. This DMF provides more useful information, saves time by executing multiple statements, and allows for the combination of results with other DMVs and DMFs. By staying up-to-date with the latest SQL Server queries and commands, database administrators can enhance productivity and flexibility in managing their databases.