sp_monitor (Transact-SQL)

 

Displays statistics about Microsoft SQL Server.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
sp_monitor  

0 (success) or 1 (failure)

Column nameDescription
last_runTime sp_monitor was last run.
current_runTime sp_monitor is being run.
secondsNumber of elapsed seconds since sp_monitor was run.
cpu_busyNumber of seconds that the server computer's CPU has been doing SQL Server work.
io_busyNumber of seconds that SQL Server has spent doing input and output operations.
idleNumber of seconds that SQL Server has been idle.
packets_receivedNumber of input packets read by SQL Server.
packets_sentNumber of output packets written by SQL Server.
packet_errorsNumber of errors encountered by SQL Server while reading and writing packets.
total_readNumber of reads by SQL Server.
total_writeNumber of writes by SQL Server.
total_errorsNumber of errors encountered by SQL Server while reading and writing.
connectionsNumber of logins or attempted logins to SQL Server.

SQL Server keeps track, through a series of functions, of how much work it has done. Executing sp_monitor displays the current values returned by these functions and shows how much they have changed since the last time the procedure was run.

For each column, the statistic is printed in the form number(number)-number% or number(number). The first number refers to the number of seconds (for cpu_busy, io_busy, and idle) or the total number (for the other variables) since SQL Server was restarted. The number in parentheses refers to the number of seconds or total number since the last time sp_monitor was run. The percentage is the percentage of time since sp_monitor was last run. For example, if the report shows cpu_busy as 4250(215)-68%, the CPU has been busy 4250 seconds since SQL Server was last started up, 215 seconds since sp_monitor was last run, and 68 percent of the total time since sp_monitor was last run.

Requires membership in the sysadmin fixed server role.

The following example reports information about how busy SQL Server has been.

USE master  
EXEC sp_monitor  

Here is the result set.

last_runcurrent_runseconds
Mar 29 1998 11:55AMApr 4 1998 2:22 PM561
cpu_busyio_busyidle
190(0)-0%187(0)-0%148(556)-99%
packets_receivedpackets_sentpacket_errors
16(1)20(2)0(0)
total_readtotal_writetotal_errorsconnections
141(0)54920(127)0(0)4(0)

sp_who (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show: