SQL Server performance baseline benchmark

Question

In new SQL Server environment, I am trying to setup an performance baseline for some of the counters. Let me explain what I am doing.

I captured the performance counters through the performance monitor and get the data (in CSV format) for around a month's time and use it to find the average which will give an baseline for monthly performance.

Counters collected:

• PhysicalDisk (_Total)\Avg. Disk sec/Read,
• PhysicalDisk (_Total)\Avg. Disk sec/Write,
• Processor (_Total)\% Processor Time,
• Memory\Available MBytes,
• SQL Server:Access Methods\Page Splits/sec,
• SQL Server:Buffer Manager\Buffer cache hit ratio,
• SQL Server:Buffer Manager\Page life expectancy,
• SQL Server:SQL Statistics\SQL Compilations/sec,
• SQL Server:SQL Statistics\SQL Re-Compilations/sec.

Can someone explain what I am doing to capture the performance baseline is correct or wrong?

Answer 1

There's a few different questions inside your post:

Q: Am I gathering the right counters?

I think you copied that Perfmon counter list from an older version of my Perfmon counter blog post. I've updated the counter list over time, so you may want to refresh it - or check with the folks where you got it, if someone in your team is keeping an offline copy.

Q: How should I gather them?

As I write in that post, I'd recommend capturing them every 15 seconds. (Every minute is fine too.) If you go any longer between captures, you can miss peak bursts of activity.

Q: Should I use the averages to build a baseline?

No - averages miss peak load, and include hours (or even days) of time when your SQL Server doesn't have any activity at all. You'll want to focus on periods of time where your end users are complaining about performance.