sp_monitor (T-SQL)

Displays statistics about Microsoft® SQL Server™.

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

Execute permissions default to members of the sysadmin fixed server role.

Examples

This 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 1993 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
sp_who System Stored Procedures
Using Variables and Parameters  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.