What are storage baselines, and why do I need them?

Question: When people talk about performance tuning, they talk about baselines. What are baselines and why do I need them?

Answer: Baselines are a known normal levels of performance that a server runs under. The whole reason that you need to gather baselines is so that you've got something to compare the current performance numbers to. You can gather the base line easily enough by using performance monitor and saving the data from perfmon either to a SQL Server database or to a binary table. Then over time you'll want to compare the current performance numbers to the numbers which were gathered to see if the system has grown and to confirm that performance is still within acceptable limits.

Gathering a performance baseline doesn't need to be very complex or hard to do. It can be as simple as setting up a perfmon counter set and running it for a few hours to get a good set of numbers to look at. Just make sure that when you gather the baseline you go it on your busiest day. To make this easier to do you might want to look into gathering the baseline for several days in a row then figure out by looking at the data which one is the right one to consider your baseline.

When it comes to what to monitor you'll want to monitor the physical disk counters as well as the buffer manager counters and memory usage counters. You'll want to watch the memory counters as well as the disk counters because if numbers like Page Life Expectancy (PLE) change when you monitor later on you need to see what those number were when you captured the baseline. As the storage and memory numbers are tied together quite closely you need to monitor both of them so that you can see where variances in the storage numbers are coming from.

If after taking the performance baseline you and you bring in a new storage engineer or consultant to diagnose storage or SQL performance problems they can use the baseline that you have captured to see how the system should be performing.

Don't forget that you will want to update your baseline from time to time. As the business and/or the number of users on the system grows the baseline needs to be updated so that it reflects the new performance numbers of the system.

Denny