Create a SQL Server Baseline with the PAL Tool

When we do a SQL Server Health Assessment, we quickly dive into SQL Server performance and Windows/Physical/Virtual resource usage counters. During our kick off interview  we always ask if you have a SQL Server baseline from the past to compare. Usually the answer is no, unfortunately.

Like monitoring and alerting, many of the tools vendors make automated baseline collection tools. Also like alerting, though, baselines of your SQL environment is something you can do yourself for free. The information you gather when you do a proper SQL Server baseline can help you in many ways. A proper baseline with periodic updates reviewed against each other help you plan for future capacity. Comparing solid baselines against current metrics show you when you are operating outside of normal conditions. All of this information, basically, empowers you to make informed decisions and spend wisely. Too many clients try and throw hardware at problems that were never hardware issues to begin with. Some blame developers for serious resource constraint caused slowdowns. Good baselines tend to help you spend on the right problem and see it well before it becomes worse.

Why Take a SQL Server Baseline?

I once heard a banking security person talk about training tellers on detecting counterfeit currency. New tellers don’t normally review counterfeit currency to detect counterfeits better. Instead, they are given real currency to handle, a lot. They then know what normal is. Therefore when the fake stuff comes across, they can see or feel it easier. They know what “normal” is.

It’s the same with a good SQL Server baseline. Any baseline, really. They work best when you start now, while things are normal.  If you never take a baseline of your particular SQL Server environment, how do you know what normal is? Frankly, you don’t. Sure, you have a guess, but you just don’t know for sure. That alone is a great reason to start taking SQL Server baselines today.

Seeing what your performance is on a “normal”  busy day. And then seeing your metrics on a “normal” slow day is important if you develop a problem later. You can then take a look at performance metrics on a busy and a slow day and compare them. Are you doing more batch requests/sec? Less? Are you sending more I/O and stressing your disks out more? Is there more indication of memory pressure 6 months after you took your initial snapshot?

Starting to watch the performance of your server only costs you a little time today. While there are many methods, using the techniques and tools described here will be an excellent start. Another side benefit for you while you go down this journey is learning! You’ll learn so much more about SQL Server performance counters and how they interact. You’ll be a better performance troubleshooter.

Why Use PAL to Take a SQL Server Baseline?

Performance Analysis for Logs (PAL) is a free tool that a support engineer at Microsoft created. It let’s you create a template for Perfmon to use.  It works through the resulting perflog to help you analyze the data easily. When this analysis is done you are given handy charts, graphs, and textual data that. This data can be used for your baseline and to investigate performance problems.

The tips in the report come from a  couple sources. From the built in Microsoft data about counters you see in perfmon. And from analysis by the developers who contributed to the PAL project. Some of the data points are based on combining several perfmon counters that relate to each other.

We always run the PAL set of perfmon counters during our SQL Health Assessments. We also often add our own list of counters based on the your environment and needs. As SQL Server Consultants we always examine the results to help get a sense for how things look.  You should do this also, and you easily can!

This is an excellent tool for you to use as you begin your journey towards collecting performance benchmarks and baselines.

Using PAL – Step by Step

PAL is easy to use. Below are a couple videos showing two of the important points to PAL. The main steps in the process I use when using PAL are below here also..

• Download the tool – It’s free and can be found here.
• Install it – I install this on a workstation machine, not on a production SQL Server! The analysis of a large log file can take time and is taxing, so I like to use it on a high-powered workstation.  I often perform PAL analysis on my laptop or tablet. You’re likely to need a few components depending on your workstation configuration, but the tool is good at telling you these things during install.
• Choose your  perfmon template – Based on the version of SQL Server you’re looking into,  the PAL tool can help you create a perfmon template and even customize it to your instance of SQL Server. Customization is important since each instance has its own perfmon counter object names. The instance name becomes part of the structure of the name.
• Collect perfmon data – I set that template up in perfmon on the SQL Server instance and collect binary (BLG) files to a disk that doesn’t have SQL Server data or log files and has enough space. I’ll collect in 15-30 second samples usually–though for baselines I may go to a longer interval, and for troubleshooting performance problems, sometimes I’ll use a lower one.
• Download the BLG files – Where possible I’ll rar the BLG files down and download them or copy them to a workstation or non-production server with PAL installed.
• Let PAL do the hard work – I’ll let PAL analyze the files. Often I’ll let it run for a while while I work on other tasks. It isn’t often quick, especially with larger files.
• Review the report – I’ll look through the resulting PAL report and look for bottlenecks. Sometimes I read the tool tips and textual tips; often I’m just looking at alerts and graphs. But that decision of what to look for come from years of working with the data in these counters and knowing what works for my analysis. Starting out new? Look at the text. Dig into blog posts and training materials whenever you find something new or something you don’t fully understand.
• Rinse/Repeat – To check how things change over time, do the same process on a periodic basis and see how the counters change.

Using PAL – Videos

A few years ago, I recorded a couple videos to add to this post. Check out the videos and get started on the road to creating your own SQL Server baseline!

In this video, I show you how to take a perfmon template from PAL and apply it to your server.

In this video, I show you how PAL reads the perfmon blg files to create its reports.

Subscribe to my feed or sign up for email updates to get enhanced videos with more examples coming up!