At the SQL Connections conference earlier this month, at the start of my talk on Making SQL Server Faster, Part 1: Simple Things, I talked about the importance of having a performance baseline so you can measure the effect of any changes made to your environment. A month ago I kicked off a survey about performance baselines, see here, to see how many of you do or do not have baselines, and why.
Here are the results:
The 'Other' values are:
-
10 x "We use a performance monitoring tool to view live trends."
-
8 x "Not enough time or resources or practical methods in place to baseline."
-
5 x "I'm trying to create one."
-
5 x "Not yet."
-
3 x "We have one, but it's pretty old."
-
2 x "New job. Created one my first week on systems I had access to. Will continue for the enterprise."
-
2 x "Our environment is seasonal and the data volumes will be varying from year on year. Hence the baseline was not useful and sometimes the baselines pointed to a resource where it's not an issue."
-
2 x "Too much stuff changes.
-
1 x "Clients are not providing details as what an ideal workload is."
-
1 x "I work for multiple companies and some of them require baselines, others do not know what baselining is. The latter is more."
The 'Other' values are:
-
10 x "All of the above."
-
6 x "Any of the above if needed!"
-
4 x "Checking the effect of a change to the system, Performance troubleshooting, Proactive tuning, Capacity planning."
-
1 x "If I weren't too busy, all the above."
Information on baselines and benchmarks
(Adapted from one of my recent Insider newsletters)
One of the sentiments that we stress several times during our IE2 course on Performance Tuning is that it's far easier to troubleshoot a performance problem when you have historical data compared to when you don't. This is called having a baseline – a set of measurements of performance metrics when the overall system is performing normally (i.e. satisfactorily for users). Some examples are:
-
The I/O load during regular and peak operations, from the SQL Server and Windows perspectives
-
Buffer pool health (e.g. Page Life Expectancy and Lazywriter activity)
-
Run-time characteristics of important queries
-
Wait statistics
Having a baseline means that when performance appears to be suffering, measurements can be taken and compared against the baseline, to identify areas that have changed. This allows you to target further investigations rather than do what I call 'flailing' or 'knee-jerk' performance tuning, where you have no idea where to start or pick a random symptom and focus on that, wasting precious time.
I could write a whole bunch about baselines, how to collect them, and all kinds of other info, but Erin Stellato has just started a multi-part article series over on SQL Server Central, so I won't duplicate that. Check out her first article: Back to Basics: Capturing Baselines on Production SQL Servers. She's also just published a 3-hour online course through Pluralsight called SQL Server: Baselining and Benchmarking, with all kinds of demos and tons of practical how-to advice.
Bottom line: your performance investigations and tuning will be a LOT easier if you have a baseline to compare against. If you don't have one, get one.