If you're a Managed Hosting (SaaS) customer, this topic doesn't apply to you.

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.

Key Areas and Counters to Monitor
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:
  • Add drives to the array to increase the spindles and spread the load.
  • Add additional channels.
  • Change the RAID version to a higher performance version (for example, RAID 5 to RAID 0+1).
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:
  • Add drives to the array to increase the spindles and spread the load.
  • Add additional channels.
  • Change the RAID version to a higher performance version (for example, RAID 5 to RAID 0+1).
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.