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
Requires Free Membership to View
bottlenecks that are worth looking for. If you measure the Disk I/O using either the Physical Disk: % Disk Time and the Physical Disk: Current Disk Queue Length (total instances) you want to keep your Disk Time Counter below 2.0. A Current Disk Queue Length above 3.0 also indicates you may have a Disk I/O bottleneck. It's a good idea to measure both counters at once to see if they correlate; and you can click on a trace to highlight it and press the Ctrl+H keystroke to highlight the selected trace.
The second bottleneck is the processor, and you should open instances of the Processor: % Processor Time Counter for each of the processors in your system. You are looking for two values: overall processor activity as well as how well the processor load is balanced. Any processor with a consistent loading of above 80% is maxed out and needs upgrading.
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation