Monitoring Server Statistics

Using the sp_monitor system stored procedure, you can display statistics that indicate how busy SQL Server has been. Each time sp_monitor is run, these statistics are returned:

last_run
The time the sp_monitor system procedure was last run.
current_run
The time the sp_monitor system procedure is being run.
seconds
The number of elapsed seconds since the sp_monitor system procedure 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.

You execute the sp_monitor system stored procedure by using any of the SQL Server client utilities (such as ISQL/w or the query window of SQL Enterprise Manager). Because it updates the spt_monitor table in the master database, you must have SA privileges to execute sp_monitor.

    To view server statistics

For example:

sp_monitor
last_run             current_run          seconds
-------------------  -------------------  -------
MAR 11 1995 3:26PM   MAR 11 1995 4:34PM   4038

cpu_busy             io_busy              idle
-------------        ----------           ------------
4250(215)-68%        67(1)-0%             109(100)-31%

packets_received     packets_sent         packet_errors
-------------------  -------------------  -------------
781(15)              10110(9596)          0(0)

total_read           total_write          total_errors   connections
--------------       --------------       ------------   -----------
394(67)              5392(53)             0(0)           15(1)

The information returned shows not only the current value for each statistic but also how much each has changed since the last time the procedure was executed. Each statistic is returned 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 the 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.

SQL Server keeps track of these statistics by using a series of predefined global variables. With the exception of last_run, current_run, and seconds, the names of the sp_monitor statistics are also the names of global variables, except that the names of the global variables are preceded by @@.

Variables are defined entities that are assigned values. Global variables are predefined and maintained by the system. Global variables are distinguished from local variables by having two "at" symbols (@@) preceding their names.

The unit of time reported by a global variable is a timetick. Each timetick on the operating system is 31.25 milliseconds (1/32 second).

Not all global variables are reported on by sp_monitor. Many of these global variables report on system activity, and their reports refer to activity since the last time SQL Server was started. You can use the SELECT statement to query the global variables directly.

These are the server global variables provided with SQL Server:

@@CONNECTIONS
Specifies the number of logins or attempted logins since SQL Server was last started.
@@CPU_BUSY
Specifies the amount of time, in ticks, that the CPU has spent doing SQL Server work since the last time SQL Server was started.
@@DATEFIRST
Returns the current value of the SET DATEFIRST parameter. Indicates the first day of each week: 1 for Monday, 2 for Tuesday, and so on through 7 for Sunday.
@@DBTS
Specifies the value of the current timestamp datatype for the database. This timestamp is guaranteed to be unique for the database.
@@IDLE
Specifies the amount of time, in ticks, that SQL Server has been idle since it was last started.
@@IO_BUSY
Specifies the amount of time, in ticks, that SQL Server has spent doing input and output operations since it was last started.
@@MAX_CONNECTIONS
Specifies the maximum number of simultaneous connections that can be made with SQL Server in the current computer environment. The user can configure SQL Server for fewer connections with sp_configure. @@MAX_CONNECTIONS is not necessarily the number configured currently.
@@MAX_PRECISION
Returns the level of precision used by decimal and numeric datatypes as currently set in the server. By default, the maximum precision is 28; however, a larger precision can be set when SQL Server starts by using the /p parameter with sqlservr.
@@MICROSOFTVERSION
A version used internally to track the current version of the server. If version checking is necessary, see @@VERSION.
@@PACK_RECEIVED
Specifies the number of input packets read by SQL Server since it was last started.
@@PACK_SENT
Specifies the number of output packets written by SQL Server since it was last started.
@@PACKET_ERRORS
Specifies the number of errors that have occurred while SQL Server was sending and receiving packets since the last time SQL Server was started.
@@SERVERNAME
Specifies the name of the local SQL Server. You must define this name with the sp_addserver system stored procedure and then restart SQL Server. The setup program sets this variable to the computer name during installation. Although @@SERVERNAME can be changed with sp_addserver, this method is not recommended.
@@SERVICENAME
Specifies the name of a running service. Currently, @@SERVICENAME defaults to the @@SERVERNAME.
@@TIMETICKS
Specifies the number of microseconds per tick. The amount of time per tick is computer-dependent. Each tick on the operating system is 31.25 milliseconds (1/32 second).
@@TOTAL_ERRORS
Specifies the number of errors that have occurred while SQL Server was reading or writing since the last time SQL Server was started.
@@TOTAL_READ
Specifies the number of disk reads by SQL Server since it was last started (this includes disk reads only, not cache reads).
@@TOTAL_WRITE
Specifies the number of disk writes by SQL Server since it was last started.
@@VERSION
Specifies the date, version number, and processor type for the current version of SQL Server.

Any user (not just the SA) can use the SELECT command to query a global variable directly.

    To query a global variable

For example:

select @@cpu_busy

------------------
4250

For more information about global variables and about using sp_monitor, see the Microsoft SQL Server Transact-SQL Reference.