Which performance counters are the best 5 to check the health of my SQL Server 2008 box

Question

I want to make sure that my Sql Server 2008 box doesn't degrade over time as I develop my application. What are the best 5 counters to keep track of?

Answer 1

You should check at least:

• Processor usage
• Current disk queue length
• SQL Server Cache hit ratio
• Network card throughput
• CPU context switches

Answer 2

Also check for blocked processes. This is a process that's waiting on a resource (usually I/O) that's in use by another SQL process.

This bit me a few times on a heavily-used SQL 2005 box on a system being used by ~700 people in 110 different locations. As soon as we got a blocking process, everything else ground to a halt until it was killed/finished.

9 times out of 10 the blocking process was waiting on disk I/O because the link to our SAN was quite saturated.

Answer 3

Brent Ozar has a blog post and very informative video on this, and he explains WHY you monitor these counters and WHAT you should expect to see and WHAT to do if things start to go wrong.

(And I'm not going to take credit for this, so it's CW)