Performance Counters

Installing SQL Server adds SQL Server—specific performance counters to the Windows 2000 System Monitor (Performance Monitor in Windows NT 4.0). You can use these counters with the standard Win2K or NT performance counters to keep tabs on your system's response to gradual load changes. To establish a baseline, begin system monitoring when the system is running as usual. Seven important performance counters can give you a quick idea of your SQL Server system's overall status.

7. Bytes Total/sec

The Bytes Total/sec counter, which is in the Network Interface object, can help you find a network-adapter bottleneck. Compare this number with your total available bandwidth. Generally, the counter should show less than 50 percent of the available bandwidth.

6. Total Server Memory

This counter, located in the Memory Manager object, shows SQL Server's overall memory usage in kilobytes. Add memory if this number is consistently higher than the amount of physical memory in the system.

5. Average Disk Queue Length

This disk-performance counter in the PhysicalDisk object shows how many requests are usually waiting for disk access. Microsoft recommends that the number of waiting I/O requests be no more than 1.5 to 2 times the number of spindles that make up the physical disk. If this counter number is consistently higher than the recommended number, you might benefit from faster disks or additional disk drives.

4. Cache Hit Ratio

This counter in the Cache Manager object tells you whether SQL Server can pull your queries' execution plans out of the Procedure Cache. Ideally, this number should consistently be higher than 85 percent. If you see the number drop over time, consider adding RAM or optimizing your queries.

3. Buffer Cache Hit Ratio

Buffer cache use is application-specific. The Buffer Cache Hit Ratio counter in the Buffer Manager object shows how often SQL Server is able to pull data from the buffer cache. The higher the number, the better; when SQL Server can find the data pages it needs in the buffer cache, it doesn't have to perform a physical I/O. If you notice that this ratio is dropping over time, consider adding RAM.

2. Pages/Sec

The Pages/Sec counter, located in the Memory object, shows the number of pages that SQL Server retrieved from disk because of page faults or that SQL Server wrote to disk to free up space in the working set. Paging spikes are typical, but this counter number should remain close to zero. An increase in paging can signal the need to add memory; you might also attempt to reduce the number of applications that are running.

1. % Processor Time

One of the most vital counters to monitor is the % Processor Time counter in the Processor object. This counter tells you what percentage of its capacity the processor is using. You'll occasionally see spikes that show periods when the system is running at 100 percent. However, if you see extended periods when usage is greater than 80 percent, your system is CPU-bound and might benefit from more processing power.