Displays statistics about SQL Server.
sp_monitor
SQL Server keeps track in a series of global variables of how much work it has done. Executing sp_monitor displays the current values of these global variables 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.
These are the columns in the sp_monitor report:
| Column | Description |
|---|---|
| last_run | The time sp_monitor was last run. |
| current_run | The time sp_monitor is being run. |
| seconds | The number of elapsed seconds since sp_monitor was run. |
| cpu_busy | The number of seconds that the server computer's CPU has been doing SQL Server work. |
| io_busy | The number of seconds that SQL Server has spent doing input and output operations. |
| idle | The number of seconds that SQL Server has been idle. |
| packets_received | The number of input packets read by SQL Server. |
| packets_sent | The number of output packets written by SQL Server. |
| packet_errors | The number of errors encountered by SQL Server while reading and writing packets. |
| total_read | The number of reads by SQL Server. |
| total_write | The number of writes by SQL Server. |
| total_errors | The number of errors encountered by SQL Server while reading and writing. |
| connections | The number of logins or attempted logins to SQL Server. |
This example reports information about how busy SQL Server has been.
sp_monitor go
last_run |
current_run |
seconds |
------------------ |
-------------------- |
------------- |
Mar 29 1993 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) |
Execute permission defaults to the system administrator, who can transfer it to other users.
master.dbo.spt_monitor
| sp_who | Variables |