The following tip, SQL Server counters, part 2 will look at more specific counters to use for monitoring SQL Server performance.
The Performance Monitor is one of three tools you can use for monitoring SQL Server's performance (the other two are the Profiler and the Enterprise Manager). With more than 156 individual counters and the ability to create your own counters (as described in SQL Server Books Online) there is an almost bewildering array of tools for you to work with. The Windows server OS offers literally hundreds of possible counters for you to analyze. The words "information overload" come to mind. However, some counter types are much more valuable than others, and your particular setup and troubleshooting will direct your needs to one set of counters or another.
You can get to the Performance Monitor from a menu command or from an icon in the Profiler toolbar. There are six counters that are installed and monitored by default in version 7.0: Buffer Cache Hit Ratio, Page Reads, Page Writes, SQL Compilations/sec, Total Server Memory (KB), and User Connections. The version of SQL Server you are using makes a difference, because in version 6.5 there were five defaults: Cache Hit Ratio, three I/O counters (Page Reads/sec, Single Page Writes/sec, and Transactions/sec), as well as User Connections. These sets of counters measure similar metrics, but have slightly different meanings and therefore interpretations.
It's a good idea to measure counters one at a time. The most important counters are probably: Disk I/O, Processor, Memory, User Connections, and Network in that order. These are the major bottlenecks in database performance (generally), and they are the ones that you can apply the appropriate corrections to. You may find that the Performance Monitor collects more data than you need, which is commonly the case; or you may find that the performance feature that you want to analyze is removed from the chart by the next scan of data. Adjust the update frequency using the Options | Chart command so that you can better view the important aspects of your results.
You'll find that you will want to add more counters when you are troubleshooting or diagnosing problems. Counters are added to the Performance Monitor using the Edit | Add to Chart command or the Plus icon on the toolbar of that utility. In selecting a counter you also need to pay attention to various instances of the particular counters you are adding. For example, some instances are database specific, and some instances aren't. SQL Server 7.0 has 22 counters that express 70 instances, for example.
Be aware that many counters will create a drag on a system's performance, so be careful that you don't install a counter on a permanent basis that will impact your database's operation negatively. You will also find that some counters only run as long as the Performance Monitor is open and are closed when you exit the utility. In the next tip we'll take a further look at this important troubleshooting and optimization tool.
The following tip, SQL Server counters, part 2 will look at more specific counters to use for monitoring SQL Server performance.
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
