Skip to content

Latest commit

 

History

History
101 lines (73 loc) · 4.37 KB

sys-dm-exec-input-buffer-transact-sql.md

File metadata and controls

101 lines (73 loc) · 4.37 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.dm_exec_input_buffer (Transact-SQL)
sys.dm_exec_input_buffer (Transact-SQL)
rwestMSFT
randolphwest
02/24/2023
sql
system-objects
reference
sys.dm_exec_input_buffer
sys.dm_exec_input_buffer _tsql
dm_exec_input_buffer
dm_exec_input_buffer_tsql
sys.dm_exec_input_buffer dynamic management function
TSQL
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

sys.dm_exec_input_buffer (Transact-SQL)

[!INCLUDEtsql-appliesto-2014sp2-asdb-xxxx-xxx-md]

Returns information about statements submitted to an instance of [!INCLUDEssNoVersion].

Syntax

sys.dm_exec_input_buffer ( session_id , request_id )

Arguments

session_id Is the session ID executing the batch to be looked up. session_id is smallint. session_id can be obtained from the following dynamic management objects:

request_id The request_id from sys.dm_exec_requests. request_id is int.

Table Returned

Column name Data type Description
event_type nvarchar(256) The type of event in the input buffer for the given session ID (SPID).
parameters smallint Any parameters provided for the statement.
event_info nvarchar(max) The text of the statement in the input buffer for the given session ID (SPID).

Permissions

On [!INCLUDEssNoVersion], if the user has VIEW SERVER STATE permission, the user will see all executing sessions on the instance of [!INCLUDEssNoVersion]; otherwise, the user will see only the current session.

Important

Running this DMV outside of SQL Server Management Studio against SQL Server without VIEW SERVER STATE permissions (such as in a trigger, stored procedure, or function) throws a permission error on the master database.

On [!INCLUDEssSDS], if the user is the database owner, the user will see all executing sessions on the [!INCLUDEssSDS]; otherwise, the user will see only the current session.

Important

Running this DMV outside of SQL Server Management Studio against Azure SQL Database without owner permissions (such as in a trigger, stored procedure, or function) throws a permission error on the master database.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Remarks

This dynamic management function can be used in conjunction with sys.dm_exec_sessions or sys.dm_exec_requests by doing CROSS APPLY.

Examples

A. Simple example

The following example demonstrates passing a session ID (SPID) and a request ID to the function.

SELECT * FROM sys.dm_exec_input_buffer (52, 0);
GO

B. Using cross apply to additional information

The following example lists the input buffer for user sessions.

SELECT es.session_id, ib.event_info
FROM sys.dm_exec_sessions AS es
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib
WHERE es.is_user_process = 1;
GO

See Also