How to Choose a SQL Server Health Check

You already know how important it is to perform a SQL Server health check. That’s why you’re reading this.

The question isn’t whether to do one but, “How?” In this article, I’ll discuss the pros and cons of the following options:

1. Roll your own
2. Download free scripts
3. Pay someone

Option 1: Roll Your Own SQL Server Health Check

10 years ago this approach was all the rage. As you’ll see in the next option, there are just too many great options available to you that are already written to make it still great.

That said, maybe you want to learn how. Maybe you want to check out PowerShell and use this as a teaching example. Go for it. You can research and learn about the DMVs, you can read posts on community sites like SQL Server Central, look up answers to questions on sites like dba.stackexchange.com (some weird people like me even ask and answer their own questions to help you learn), or check out posts like the tips in this Straight Up SQL Server Tips series.

There are benefits to this approach – you’ll learn a lot, you’ll understand how your environment works, and you’ll potentially see things you weren’t looking for because you aren’t following a preexisting script which has your route already planned.

This roll your own SQL Server health check approach is great for:

• Tinkerers
• Folks looking to combine learning with a health check
• People with a lot of time on their hands who are casually looking for answers, learning, and not in a rush.

It has a $0 cost on the face of it, but there are hidden costs in processing the results, understanding them and then knowing exactly what you should do with all that data.

Option 2: Three SQL Server Health Check Scripts (that are free)

I am proud to be part of the SQL Server community. Even the members of the community who have a primary business of providing paid SQL Server consulting services give their time, energy and resources to you – the SQL Server users.

I’ve started to write scripts many times. But if you look, you’ll see various components out there (like my SQL Server configuration check script). I’ve scrapped those efforts because of the great, free scripts like these being so widely used and available. Well-known names have come up with their own versions of SQL Server health check scripts and tools. There are likely a half dozen or more approaches you can go down but let’s talk about three of my favorite.

SQL Server Health Check Script from Glenn Berry

Glenn Berry is a wizard when it comes to hardware and SQL Server.  He’s a great consultant and a super smart guy. I don’t remember how long he has published his “SQL Server Diagnostic Information Queries,” but it has been for a fairly long time, longer than the other two approaches.

Download Glenn’s SQL Server Diagnostic Information Queries

He updates these scripts monthly. Basically, he gives you a set of scripts to use – one per version of SQL Server that he supports. The scripts are just single .sql files. Inside of these scripts are checks for many many many things that are likely hurting your server right now. Go check out Glenn’s scripts!

BUT Wait – don’t just click execute on the whole script!

You should never do that with internet scripts, even if it is a free SQL Server health check script from an MVP, expert and all around swell guy. He has many checks, and some are more expensive in terms of resources than others. Some are meant to run in your user databases, etc.

Glenn’s Scripts are great for: People who like seeing an array of script options in front of them, want to pick up the script(s) they need to run, and love having some cheat sheet/notes on what you are looking for when you look at the results. $0 cost to run, and I often grab his diagnostic queries when I want to quickly see wait stats that are filtered, or missing index details. Sure I have those scripts, but it’s easier to just grab.

The Blitz Family

What can I say? Brent Ozar is a giver.

Over the years he has built and commissioned developers to help him build what has become the blitz family of scripts. I’ve blogged about this before when talking about why I love the free community tool. Brent’s scripts generally are run all at once, the results are tabulated and there is some prioritization given. You can run Blitz, and pretty much give yourself a set of tasks to do on your environment. He’s recently put the project out on github so you don’t even have to give your e-mail if you don’t want (but you should, his organization posts great tips and tricks and he has all sorts of great training conten, and often sends out deals. He’s the only SQL Server trainer I know with Black Friday deals 😉 ).

Download Brent Ozar Unlimited’s First Responder Kit

Brent’s SQL Server health check scripts are great for: People who want to run a single script themselves, maybe don’t have the budget to invest in an external resource but still want to get a little help with knowing what to do with the results.

DBATools.IO

I love this PowerShell library for DBAs. Rather than write a lot about it here, I’ll link you to a post where I applauded this tool as one of my favorite free tools out there. Short story here is – for most of the important checks out there, they probably have a module for you. Just go visit their library of commands and be amazed.

Browse dbatools PowerShell library

This is Great when: You want an excuse to learn PowerShell, you like taking pieces from here and pieces from there and want to organize a standard approach for yourself to follow doing checks.

Option 3: Pay for a SQL Server Health Check

Yesterday, I had a pool company come over to the house to close our pool. I know how to close my pool. I pay a great local kid to come and help stack firewood and cut down trees with me across our farm here. I am physically capable of doing all of that by myself. Why do I do it? Because I’m busy. Because I need to focus. And with the pool closing? I’d rather not miss a step and forget to drain something or properly blow out a pipe and have a crack due to freezing. When you add in the opportunity cost for me doing the work myself and not being billable or stealing even more time from my family, or the cost of making a mistake closing the pool? It becomes a bit of a no-brainier for me to hire out for that task.

I’m biased here because we sell a SQL Server Health Check. But here’s the deal: 2 out of the 3 free script writers above also do. So many companies do. You don’t have to use our SQL Server health check, though I have a few reasons why you might consider it.

• The pricing we have is pretty aggressive, especially when you consider it is for up to 3 instances.
• Our approach is flexible.
• We spend hours mentoring you before and after the health check.
• Our document of all findings and personalized action plan is a nice touch.

But for a lot of you, the paid SQL Server health check is perhaps the better approach. Go visit those pages from the script. Do a Google search for SQL Server health check. I really don’t mind if you work with another of the great companies out there to get it done, or if you use the free health check scripts.

But when you pay for a SQL Server health check, you have a person who has been there and done that performing the check (hopefully!). You have a consultant who has seen worse-off environments than yours and lived to tell about it. There are things the scripts just can’t detect. Nuances. Things that only come out in the initial interview with your SQL Server health check consultant and come out when the mentoring call happens discussing the findings and your action plan.

This is great for: Those companies that know they should, know where the scripts are but just haven’t yet. It’s great when you have 99 problems and a SQL Server health check isn’t one. It’s great when you want a curated tour of your SQL Server and all that’s good and bad with it. It’s great when you are an in industry where telling your customers “Hey look, I have a seal of approval from a SQL Server expert!” makes you stand out from the competition. Yes, it costs money.  Depending on the provider, it could be anywhere from probably $1,000 up to $4,000 per instance.

The point is – you need to be checking on your SQL Server health. And if you never have? There are probably some really basic issues accumulating which will one day conspire against you. Whether you check out the scripts, roll your own, or engage with a consultant – please, check your SQL Server’s health.