SQLTeam.com | Weblogs | Forums

SQL Server Baseline, how to start?

Hi, everyone :slight_smile:

i'm a bit desperate. For some time I have been looking for baseline scripts and what should be tracked. But since I've found so many scripts and so many different approaches, I'm a bit at a loss. Our company doesn't have the budget for a monitoring system like red gate or something similar, so I'm thinking of solving this with board resources (scripts, etc.).
Unfortunately, I'm missing a start, what and how exactly to take notes? Maybe you have experiences etc. that you would like to share with me? :slight_smile:

Not to pile on, since you've already researched a few things, but I'd recommend some additional resources, just in case you haven't seen them before:

SQLSkills is once place I stop often to find good information, and Erin has some baselining topics:

One is her Accidental DBA entry on baselines:

There's a pretty good video on the PASSTV channel on Youtube:

I've set it to a point in time where they start discussing what things to capture, but you should check the whole video as they describe what baselining is about.

Note that some of the typical baselining topics (particularly Page Life Expectancy) have changed in priority over the years. The video discusses that, as well as some other counters. Disk I/O in particular has completely changed in the past 10 years with SSDs, any baseline or collection advice on that needs additional consideration if it's more than 5 years old. For instance:

Allen discusses some counters and attributes whose relevance has changed over time, but IMHO his overall approach is a bit outdated (the presentation is 7 years old at this point). There's still useful information, but I don't know if his techniques would help you. PowerShell does make it easy to collect performance counters and extract Windows logs, that is definitely worth your time if you haven't considered it yet.

1 Like

Thank you very much Robert for your answer :slight_smile:
I also found Erin's posts and found them all very informative so far. I've been busy with the metrics etc. for a while and I think I'm quite fit. Just the start of baselining, what you should track and how is still a problem for me. I've already done a lot with Powershell, especially dbatools, etc. But I have the problem that the performance counters are sometimes drawn from the OS and unfortunately our systems are all in German. so I would first have to translate all counters, etc.
that's why I'm looking for a baseline via SQL Script. :slight_smile:

The only performance counters you can access via T-SQL are the SQL Server perf counters, via SELECT * FROM sys.dm_os_performance_counters. Those won't give you things like disk I/O and OS level memory or CPU metrics, but PowerShell or Perfmon (Performance Monitor) can get those.

If in doubt about what to collect, err on the side of collecting more than you need at first. You can review it after a few days and analyze it for useful patterns, and discard it or stop collecting it if you don't see any.

A "useful pattern" would be any change in a metric that you can definitely associate with an event, or another pattern in other metrics that have some correlation (e.g. you rebuilt an index and saw an increase in Disk I/O or memory usage, or both) This is especially useful and easier if you have scheduled SQL Agent jobs like for backups and such, you can get start and end times from the job history and review your performance counters during that time.