In the last tip we looked at the Performance Monitor and discussed its default behavior. Here we will look further at some of the specific counters that you need to pay attention to.
There are some obvious
The four memory counters: SQL Server: Buffer Manager: Cache Hit Ratio and Lazy Writes, Memory: Page Faults/sec and Paging File: % Usage are all worth monitoring. Any Cache Hit Ratio below 80% may indicate you need more memory to store cached data, as does excessive page faults or paging file usage. Lazy writes should usually be 0, a positive value indicates that pages are being written (flushed) to disk. All these counter are indications of adequate memory.
Don't forget to also look at your number of user connections and your network performance. The useful connection counters are: SQL Server: General Statistics: User Connections and SQL Server: Memory Manager: Connection Memory (KB). These counters should be analyzed in concert with the CPU utilization, the number of Logins/sec, as well as the Granted Workspace Memory, as all relate to establishing and maintaining your connection traffic.
If you are measuring disk performance to collect a set of statistics, then you need to turn on the diskperf command – it is left off to keep performance from being impacted. At the command prompt diskperf –y will start this service up, as well as opening the Devices control panel and turning it on there. You may have to restart your system in order to have the Performance Monitor accept the new setting. You save your settings to a PMC file, a file which is specific to your instance of SQL Server. The Performance Monitor can also save log files, as described in PerfMon's online help.
PerfMon was created as a developer's tool, as well as one used by end-users. You have the capability to create your own counters. Check SQL Server Online to see how this is done. For example, you can use a custom counter to monitor incoming traffic from a specific server, and almost anything else you can imagine.
There's a lot of information to be had on the subject of SQL Server performance counters. In addition to books online and the Microsoft Web site (both www.microsoft.com/sql as well as TechNet), you'll also find a lot of information on the topic on www.sql-server-performance.com . Although nearly every book on SQL Server covers counters to some degree, there are several books on performance tuning that take up the topic in greater depth. You'll find a list of books on the aforementioned Web site by clicking on the books tab at the top of their site.
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.
This was first published in March 2005