SQL Server 2000-2005-2008 Performance Counters

Memory

Memory: Pages/sec
    Average 0 - 20
    > 20 Normal
    Average > 20 indicates a memory problem

    Process: Page/Faults sec will reveal if SQL or other application
Memory: Available Bytes
    > 50MB
SQL Server Buffer Manager: Buffer Cache Hit Ratio
    Good > 90%
    Great > 99%
    Need more memory, maybe < 90%

Physical Disk

Physical Disk:Disk Reads/sec
Physical Disk: Disk Writes/sec

    These two wil depend on the throughput of your disk IO sytem, so its an indication of what its doing           rather than a potential bottleneck, look at the memory settings in conjunction with this information. 

Physical Disk: % Disk time
    Problem >55% more than 24 times in a 24 hour period

Physical Disk: Avg Disk Queue Length
    Problem >2 for each physical disk for sustained periods 20 mins or more
    if a 10 disk array > 20 is a problem

    If %Disk Time > 55 percent and Avg Disk Queue Length > 2 for extened periods you have a Disk IO
    problem.

Physical Disk % Free Space
Logical Disk: % Free Space
    Free Space is the Silent Server Killer, dont let it happen to you.
    Set an alert to run when theres less than 20% left.

CPU

Processor:%Processor Time
    If your CPU _Total figure exceeds 90% continuously you have a cpu bottleneck.
System: Processor Queue Length
    Per CPU > 2 is a problem
    8 CPU box > 16 continuously indeicates a problem

Network

Network Interface: Bytes received/sec
Network Interface: Bytes sent/sec
Network Interface: Bytes/sec
Network Interface: Output Queue Length

General

SQL Server:General:User Connections
    Problem > 255 for 10 mins +


Smlogsvc Service

This is the service that runs Performance Tools