Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

You should check at least:

  • Processor usage
  • Current disk queue length
  • SQL Server Cache hit ratio
  • Network card throughput
  • CPU context switches
share|improve this answer

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.

share|improve this answer

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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.