...So you can start building a water weenie.

Sure, you can spend your whole summer working on SQL Server performance tuning, but wouldn’t you really rather build and operate your own water weenie, like Windell here?

I thought so.

Windell is a maker, and every smart maker knows you can get so far down into the summer weeds optimizing your SQL Server databases that suddenly you realize it’s already autumn. By then, it’s too late to use your water weenie. Better luck next year.

Plenty of DBAs bounce among performance problems and user complaints in firefighting mode because they can’t make substantive progress in their SQL Server management efforts. That’s a drag, because when your database servers run efficiently, you get more from your organization’s hardware investment and you free up system resources for tasks like executing business reports and ad hoc queries.

And building your water weenie.

You can find plenty of general information on data points like performance counters and dynamic management objects (DMOs), but you can’t find much information on interpreting and actually using those data points. That’s why our Patrick O’Keefe put together Ten Tips for Optimizing SQL Server Performance, a white paper focused on SQL Server 2008 and 2012. There’s no definitive list of all the tips you need, but you’ll find that the white paper is a solid start. In this post and my next one, I’ll highlight some of those tips.

Tip #10: Baselining and benchmarking

Before you start your SQL Server tuning, you should create a baseline. Tip #10 in the paper emphasizes that “the Baselining and Benchmarking methodology helps you spot problems.” Having a record of current system performance before you start making changes is the best way to ensure you’ve improved things when your optimization process is over.

Here are a few important steps in baselining, to supplement the steps shown in Figure 1:

• Determine your goals. It’s easy to end up deep in the weeds when you overdo analysis and tuning. Are you trying to meet SLAs? Address issues in production? Deal with complaints from users? State a goal and keep your eye on it.
• Establish a norm. Create a list of the metrics you want to track. Your target here is not only a series of averages but also the deviations from those averages, since that’s where your resources will thin out.
• Collect enough data. System administrators, end users and application administrators know the usage patterns and can help you calculate an adequate period of time for data collection.
• Analyze the data. Did your changes have the intended effects? Were there any unintended consequences? Can you see where they came from?
• Do it again. Tuning should be an iterative process. Baselining and benchmarking provides the map, reference and point of departure for the work you’ll perform in optimizing your SQL Server databases.

What’s next?

Have a look at our white paper, Ten Tips for Optimizing SQL Server Performance. You’ll find several new places in your environment to eliminate the bottlenecks, rogue queries and cache thrashing that slow down your databases.

Once you’ve got all that under control, grab some latex tubing and get to work on your own version of Windell’s water weenie.