Use sp_monitor to view SQL server statistics

SQL Server provides the sp_monitor stored procedure for you to view SQL server performance statistics, including CPUNetworkIO.

SQL Server provides the sp_monitor stored procedure for you to view SQL Server performance statistics, including CPU, Network, and IO.

SQL Server provides the sp_monitor stored procedure for you to view SQL Server performance statistics, including CPU, Network, and IO. With this information, you can have a general understanding of your database performance.

The following example shows how busy SQL Server is:

Explanation of each column Name:

Column Name Description

Last_run

The time when sp_monitor was last run.

Current_run

The time when sp_monitor is run.

Seconds

The number of seconds that sp_monitor has elapsed since running.

Cpu_busy

The number of seconds that the CPU of the Server computer takes to process SQL Server.

Io_busy

The number of seconds that SQL Server spends on input and output operations.

Idle

Number of seconds when SQL Server is idle.

Packets_received

The number of input data packets read by SQL Server.

Packets_sent

Number of output data packets written by SQL Server.

Packet_errors

Number of errors that SQL Server encounters when reading and writing data packets.

Total_read

The number of SQL Server reads.

Total_write

Number of SQL Server writes.

Total_errors

Number of errors that SQL Server encounters when reading and writing data.

Connections

The number of times that SQL Server is logged on or tried.

For each column, statistics are output in the format of number (number)-number % or number (number. The first number is the number of seconds (for cpu_busy, io_busy, and idle) or the total number (for other variables) since the SQL Server is restarted ). The number in parentheses refers to the number of seconds or the total number since the last execution of sp_monitor. The percentage is the percentage of time since the last execution of sp_monitor. For example, if cpu_busy is shown as 4250 (215)-68% in the report, the CPU has been operating for 4250 seconds since SQL Server was last started; since the last run of sp_monitor, the CPU usage is 215 seconds. Since the last execution of sp_monitor, the CPU usage is 68% of the total time.

To save data, use the following system statistical functions:

Table 1 System Statistical Functions

FunctionDescription

@ CONNECTIONSThe number of connections or attempted connections.

@ CPU_BUSYTimer ticks that the CPU has been working for SQL Server.

@ IDLETime in timer ticks that SQL Server has been idle.

@ IO_BUSYTimer ticks that SQL Server has spent padding Ming I/O operations.

@ PACKET_ERRORSNumber of network packet errors that have occurred.

@ PACK_RECEIVEDNumber of packets read from the network.

@ PACK_SENTNumber of packets written to the network.

@ TIMETICKSNumber of millionths of a second in a timer tick.

@ TOTAL_ERRORSNumber of read/write errors during I/O operations.

@ TOTAL_READNumber of disk reads.

@ TOTAL_WRITENumber of disk writes.