System (Performance) Monitor
The System Monitor is not a tool that is part of SQL Server 2000, but a tool included with Windows 2000. The System Monitor allows you to monitor both Windows 2000’s and SQL Server 2000’s performance, and is a great tool for monitoring and analyzing performance. It can be run by anyone with Windows 2000 administrative rights on your SQL Servers. SQL Server 2000 system administrative rights alone will not allow you to run System Monitor.
System Monitor has the ability to monitor several hundred Windows 2000 performance indicators (called counters), and over 110 SQL Server 2000 counters, more than enough to monitor and troubleshoot SQL Server 2000’s performance.
While System Monitor offers you an abundance of counters you can measure, in most cases you will only want to monitor a handful of them, saving the more obscure counters for special situations. While you might think that you might only need to monitor SQL Server 2000 counters, and not Windows 2000 counters, this is not the case. More often than not, you will probably spend more of your time monitoring Windows 2000 counters than SQL Server counters. This is because SQL Server’s performance is greatly dependent on how Windows 2000 performs.
What Counters Should You Watch?
As mentioned previously, there are a handful of Windows 2000 and SQL Server 2000 counters that you may want to monitor on a regular basis. Here are some of the key counters you may want to watch to help you identify potential performance problems.
-
To help identify potential CPU performance issues, the Windows 2000 System Object: % Total Processor Time counter measures the average of all the CPUs in your server. This is the key counter to watch for CPU utilization. If this counter exceeds 80% for continuous periods (over 10 minutes or so), then you may have a CPU bottleneck. If you do have a bottleneck, then potential solutions include reducing the workload on SQL Server, getting faster CPUs, or getting more CPUs.
-
To help identify potential memory performance issues, the Windows 2000 Memory Object: Pages/Sec counter is important to watch. It measures the number of pages per second that are paged out of memory to disk, or paged into memory from disk. Assuming that SQL Server is the only major application running on your server, then this figure should average nearly zero, except for occasional spikes, which are normal. If this counter exceeds 0 for continuous periods (over 10 minutes or so), then you may have a paging-related problem. These can occur if you are running programs other than SQL Server on the server, or if you have turned off dynamic memory configuration on SQL Server.
-
To help identify potential I/O performance problems, the Windows 2000 PhysicalDisk Object: Avg. Disk Queue Length counter is critical to monitor. If the Avg. Disk Queue Length exceeds 2 for continuous periods (over 10 minutes or so) for each disk drive in an array, then you probably have an I/O bottleneck for that array. Ways to remove this bottleneck include: adding drives to an array (if you can), getting faster drives, adding cache memory to the controller card (if you can), using a different version of RAID, getting a faster controller, or reducing the workload on SQL Server.
-
To help identify if your server has enough physical RAM, the SQL Server 2000 Buffer Manager Object: Buffer Cache Hit Ratio counter needs to be monitored. This counter indicates how often SQL Server goes to the buffer, not the hard disk, to get data. In OLTP applications, this ratio should exceed 90%. If it doesn’t, then you need to add more RAM to your server to increase performance, or reduce the workload on SQL Server.
These are just a few of the Windows 2000 and SQL Server 2000 counters that you can monitor and use to help performance tune your SQL Server-based application.
How to Best Use System Monitor
Essentially, System Monitor offers two major ways to collect and analyze Windows 2000 and SQL Server 2000 counters. You can both collect and graph them in real time, or you can collect the data in log files and then graph and analyze them later.
The real time collection and graphing option is best when you want to perform testing on your server and receive immediate feedback. It is also handy when troubleshooting specific performance-related problems. This real time mode collects data, by default, every second, and displays in on a graph as it collect the data. You can collect and graph several different counters all at the same time. This can be very useful as it is often important to see how related counters work in unison.
While real time analysis is often handy, it is generally much more useful to log data over a period of time, and then analyze it later at your leisure. The System Monitor allows you to select which counters you want to collect, and how often to collect them. For example, you might want to collect information on 20 counters, every 60 seconds, for a 24 hour period. Or you might want to collect information on 50 counters, every 600 seconds, for 30 days. Once the data is collected, you can analyze it in the form of charts by using the System Monitor, or if you like, you can also export the data to a database or spreadsheet for a more detailed analysis.
If you are serious about monitoring the performance of your SQL Servers, I highly recommend that you collect performance data all the time on key counters, and then use trend analysis (which can be done in Microsoft Excel) to identify performance trends. For example, you can use the data you collect, and trend analysis, to help predict future SQL Server hardware needs, such as a need for more CPUs, faster I/O, or more memory. Trend analysis lets you project historical data into the future, which can be great evidence to show your boss if you are trying to justify hardware upgrades to your current hardware, or replacement hardware for your current servers.
System Monitor is a great tool, and you need to take the time to learn how to master it. You will find it very handy for troubleshooting performance problems and helping you to quantify your future hardware needs.
]]>