Monitoring SQL «Prev  Next»

Lesson 6 System performance
Objective Monitor SQL Server overall system performance.

Overall System Performance for SQL Server

SQL Server provides tools with which you can check its performance. By using the sp_monitor stored procedure you can display statistics on CPU, disk I/O, network I/O, and connections. This procedure is used to get a high-level overview of SQL Server behavior. The syntax of sp_monitor is sp_monitor

overall-system-performance

View the code below to see the output of the sp_monitor.
  1. Last_run is the last time that sp_monitor was run.
  2. Current_run is the current date/time.
  3. Cpu_busy is the number of seconds that the cpu was busy processing SQL Server requests.
  4. Io_busy is the number of seconds that SQL Server was doing input or output.
  5. Idle is the number of seconds that SQL Server was not doing anything.
  6. Packets_received is the number of network packets received by SQL Server.
  7. Packets_sent is the number of network packets sent by SQL Server.
  8. Packets_errors are the number of network errors.
  9. Total_read is the total number of disk reads performed by SQL Server.
  10. Total_write is the total number of disk writes performed by SQL Server.
  11. Total_errors is the total number of disk errors that occurred for I/O initiated by SQL Server.
  12. Connections is the total number of connection attempts that were made to SQL Server.

SP Monitor Output

What do the numbers mean?

Many of the columns output by sp_monitor have two or three numbers.
  1. The first number is the number of seconds since SQL Server was restarted.
  2. The second number, which is in parenthesis, is the number of seconds since sp_monitor was last run.
  3. The third number is a percentage.

Interpreting sp_monitor

This high-level information can be useful for finding out what is going on with SQL Server. For instance, you will want both error columns to have a value of zero. If the CPU Busy is larger than 90%, you will probably want to have more or faster CPUs. If the I/O Busy column is larger than 90%, you will want to add more disk subsystems to spread I/O out.

After installing SQL Server, you must run sp_monitor once before the information returned is meaningful. The first time it is run, the counters will not be correct.

SQL System - Quiz

Click the Quiz link below to test your knowledge of the concepts presented in this module so far.
SQL System - Quiz
The next lesson will cover how to use the database consistency checker to check system performance.