Bob here.  Database performance is a fun topic.  ”I’m running reports overnight.  They take 3 hours!”  Not necessarily the end of the world (still waiting for me in the morning).”There’s a customer on our web site.  It takes 15 seconds to see their order status.”  Ok, now we’ve got a problem.

“Sooooo…let’s buy new hardware.”  That’s the quickest and often the easiest (in terms of justifying the expense) sell to management.  Because more…more cpu, more memory, more disk…that’s going to fix it, right?

Not necessarily.  If you’re not measuring what was actually happening during those 15 seconds, then you don’t know why it took so long.  That’s why we have Performance Counters.  Metrics we can get (in real time, if needed) to tell us what’s actually going on.  Why it took 15 seconds (or 3 hours, for that matter).  And the conclusions we can come to from these metrics?

Two words:

It depends

Read on!

This article excerpt, by Robert L. Davis, originally appeared here:
When you’re working through some vague performance issues (e.g., “SQL Server seems slow today”), one of the common things to do is to collect some performance counters. If you are collecting performance counters for the first time on the server, you don’t have anything to compare them against. This usually leads to searching the web for resources that will tell you what numbers the counters should be. And sadly, it quite often ends with either misleading advice or with a disappointing message of “it depends.”
There are several reasons why we say that the target values for performance counters depend, and why we say that you need to baseline your systems.
•Workloads and server configurations vary wildly.
•Workloads change.
•SQL Server environments are constantly evolving.
So we tell you to baseline to know what your system generally looks like when it’s healthy. If you are baselining, you can compare your performance counters to last week’s numbers or last month’s number or even last year’s numbers. But if you’re not already baselining, and you have an emergent issue that you need to investigate right now, this advice is not going to help with this issue. Most of the performance counters are not going to be very helpful, and you will need to dig into the current activity on the server. Look for obvious things like blocking and extremely high degrees of parallelism. Look at the wait statistics for the currently active requests and try to determine if we have an issue with memory or CPU utilization or other bottlenecks.