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
View the code below to see the output of the sp_monitor.
Many of the columns output by sp_monitor have two or three numbers.
The first number is the number of seconds since SQL Server was restarted.
The second number, which is in parenthesis, is the number of seconds since sp_monitor was last run.
The third number is a percentage.
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.