Here is the list of counters I used to use when I was a DBA. It is based on the SQL Server Operations Guide from Microsoft (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx). Many of the ones I used match the ones in Steve's article but there are some on this list that you might not see an immediate need for. The purpose of some of these is to establish a performance baseline so that when a user complains about how performance has "suddenly" gone to pot, you can compare the current counters to the baseline to see if the problem really is related to SQL Server.

Here is my list:

Memory

• Pages/sec.

Network Interface

• Bytes total/sec.

Physical Disk

• Disk Transfers/sec.

Process

• % Processor Time for sqlservr.exe

Processor

• % Processor Time

SQL Server: Access Methods

• Full Scans/sec.

SQL Server: Buffer Manager

• Buffer Cache Hit Ratio

SQL Server: Cache Manager

• Cache Hit Ratio

SQL Server: Databases (All Instances)

• Log Growths
• Percent Log Used
• Transactions/sec.

SQL Server: General Statistics

• User Connections

SQL Server: Latches

• Average Latch Wait Time

SQL Server: Locks

• Average Wait Time
• Lock Timeouts/sec.
• Lock Waits/sec.
• Number of Deadlocks/sec.

SQL Server: Memory Manager

• Memory Grants Pending

System

• Processor Queue Length (Threshold: Average of five samples > 2)