In its Troubleshooting Performance Problems in SQL Server 2005 document, Microsoft recommends monitoring the key areas and counters in SQL Server listed in the following table.
|Area Name: Counter Name||Description|
|Memory: Pages/sec||Rate at which pages are read from or written to disk during hard page faults
As the memory pages are transferred to and from the relatively slow disk storage, the system will experience slow performance. Although it may spike occasionally, this counter should average 20 or less. If the counter is too high on a regular basis, add memory to the server.
|Memory: AvailableBytes||Amount of physical memory available for allocation At least 5MB of RAM should be available. If the amount available is less than 5MB, consider adding RAM to the server.|
|Network Interface: Bytes Total/sec||Per-second rate at which data is passing though the network interface card (NIC)|
|Physical Disk: %Disk Time||Percentage of time that the disk is busy Although it may spike occasionally, this counter should be less than 55% during any sustained period. If the counter is too high, consider doing one or more of the following:
|Physical Disk: Avg. Disk QueueLength||Number of disk requests that are waiting in the queue According to queuing theory, this counter should be less than 2 over any sustained period or the queue could become backlogged. If the counter is too high, consider doing one or more of the following:
|Processor: %Processor Time||Time that the processor is doing actual work This value is calculated by measuring the percentage of time that the processor is idle and subtracting that from 100 to get the time the processor is busy doing work. Although it will spike occasionally, this counter should be less than 80% over any sustained period. If this counter is too high, consider adding or upgrading the processors on the server.|
|System: Processor QueueLength||Number of threads in the processor queue Even on computers with multiple processors, a single queue exists for processor time. If this exceeds 2 per processor (that is, 4 on a 2-processor server or 8 on a 4-processor server), consider adding or upgrading the processors on the server.|