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 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	Last runSp_monitor.
Current_run	This operationSp_monitor.
Seconds	Sp_monitorThe number of seconds that have 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 columnNumber(Number)-Number% OrNumber(Number. FirstNumberThe number of seconds since the SQL Server is restarted (Cpu_busy,Io_busyAndIdle) Or the total number (for other variables ). In parenthesesNumber
Indicates the last runningSp_monitorThe number of seconds or the total number. The percentage is from the last run.Sp_monitorTime percentage since. For exampleCpu_busyDisplayed as 4250 (215)-68%, the CPU has been running for 4250 seconds since SQL Server was last started.Sp_monitorThe CPU has been running for 215 seconds.Sp_monitor
68% of the total time.

 

To save data, use the following system statistical functions:

 

Table 1 System statistical functions

 

Function	Description
@ Connections	The number of connections or attempted connections.
@ Cpu_busy	Timer ticks that the CPU has been working for SQL Server.
@ Idle	Time in timer ticks that SQL server has been idle.
@ Io_busy	Timer ticks that SQL Server has spent ‑ming I/O operations.
@ Packet_errors	Number of network packet errors that have occurred.
@ Pack_received	Number of packets read from the network.
@ Pack_sent	Number of packets written to the network.
@ Timeticks	Number of millionths of a second in a timer tick.
@ Total_errors	Number of read/write errors during I/O operations.
@ Total_read	Number of disk reads.
@ Total_write	Number of disk writes.