sp_monitor (Transact-SQL)

Applies to: SQL Server 2005 and later

Monitors the execution of Transact-SQL statements and procedures.

Syntax

sp_monitor
    [ [ @spid = ] spid ]
    [ , [ @clientonly = ] clientonly ]
    [ , [ @currentowner = ] currentowner ]

Parameters

Parameter	Description
`@spid`	The session ID of the process to monitor. If NULL, all active processes are monitored.
`@clientonly`	Specifies whether to monitor only client processes (1) or all processes (0). The default is 0.
`@currentowner`	Specifies whether to monitor only processes owned by the current user (1) or all processes (0). The default is 0.

Return Value

Returns 0 for success or a non-zero number for failure. No result set is returned.

Permissions

Requires membership in the sysadmin fixed server role.

Remarks

sp_monitor is used to examine the resource usage of processes running on the SQL Server. It provides information about CPU time, memory usage, and I/O operations for each process. This stored procedure is useful for identifying performance bottlenecks and resource-intensive queries.

The output of sp_monitor is displayed in the SQL Server Management Studio (SSMS) Messages window. You can use the Activity Monitor in SSMS for a more interactive and detailed view of server activity.

Examples

A. Monitor all active processes

EXEC sp_monitor;

B. Monitor a specific process

EXEC sp_monitor @spid = 55;

C. Monitor only client processes

EXEC sp_monitor @clientonly = 1;