sp_monitor (Transact-SQL)

Displays statistics about Microsoft SQL Server.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_monitor

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name Description

last_run

Time sp_monitor was last run.

current_run

Time sp_monitor is being run.

seconds

Number of elapsed seconds since sp_monitor was run.

cpu_busy

Number of seconds that the server computer's CPU has been doing SQL Server work.

io_busy

Number of seconds that SQL Server has spent doing input and output operations.

idle

Number of seconds that SQL Server has been idle.

packets_received

Number of input packets read by SQL Server.

packets_sent

Number of output packets written by SQL Server.

packet_errors

Number of errors encountered by SQL Server while reading and writing packets.

total_read

Number of reads by SQL Server.

total_write

Number of writes by SQL Server.

total_errors

Number of errors encountered by SQL Server while reading and writing.

connections

Number of logins or attempted logins to SQL Server.

Remarks

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.

Permissions

Requires membership in the sysadmin fixed server role.

Examples

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

USE master
EXEC sp_monitor

Here is the result set.

last_run

current_run

seconds

Mar 29 1998 11:55AM

Apr 4 1998 2:22 PM

561

cpu_busy

io_busy

idle

190(0)-0%

187(0)-0%

148(556)-99%

packets_received

packets_sent

packet_errors

16(1)

20(2)

0(0)

total_read

total_write

total_errors

connections

141(0)

54920(127)

0(0)

4(0)

See Also

Reference

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

Other Resources

Using Variables and Parameters (Database Engine)

Help and Information

Getting SQL Server 2005 Assistance