Imagine you inherited a new SQL Server instance, or even several instances, and you need to perform a quick “sanity check” to see if anything is off. There are probably a hundred different things a Database Administrator (or someone who has to play the role of the DBA) would want to check to see to determine the state of a SQL Server instance’s health.
We do this every day here at Straight Path using sp_CheckHealth, and we wanted to share it with you.
Here at Straight Path Solutions, we’re big fans of community tools like sp_WhoIsActive, Brent Ozar’s First Responder’s Kit, and Erik Darling’s suite of helpful stored procedures. As database administrators who are constantly looking at new clients and new servers, we wanted one tool we could run the moment we sit down in front of an unfamiliar instance to get the lay of the land and see what needs our attention first. Like many of you, we’ve been using sp_Blitz for years, but we wanted a tool that was more focused on administration-related issues. So, we wrote one.
You can download sp_CheckHealth now from our GitHub repository, along with several other helpful tools for SQL Server.
What does sp_CheckHealth do?
This tool will give you a fast, comprehensive picture of a SQL Server instance. It gathers the kind of information you would otherwise collect by clicking through a dozen dialogs and running a handful of scripts, and it flags potential issues so you can decide what to deal with first. The findings are organized into categories like Recoverability, Security, Availability, Integrity, Reliability, and Performance, and each one comes with details and an action step so you aren’t left guessing about what to do next.
This tool has several modes that present a different set of data, depending on what you want to examine.
- @Mode = 0: only the problematic issues we could find.
- @Mode = 1: instance information only, with one finding per row.
- @Mode = 11: instance information only, returned as a single row. This is handy if you want to collect the same facts from many instances and stack them together, especially if you have a Central Management Server that you can query.
- @Mode = 99: instance information and problematic issues together (the default).
How do I use it?
Execute the script to create sp_CheckHealth in the database of your choice.
Executing it without using parameters runs @Mode = 99 and returns a single result set that includes your instance information followed by any problematic findings, sorted so the most important items are at the top.
Although you can simply execute it as is, there are several optional parameters.
@Help – the default is 0, but setting this to 1 will return some helpful information about sp_CheckHealth and its usage in case you aren’t able to read this web page.
@Mode – see the previous few paragraphs to decide which Mode you want to use.
@DatabaseName – the default results include information for all databases, but use this parameter if you have a specific database that you are reviewing. Using this parameter can greatly reduce the results.
@Override – by default the stored procedure will quit and notify you if you run it on an instance with 50 or more databases, since collecting the history on that many databases can use substantial resources. Use this parameter to override that and run the full check.
@VersionCheck – the default is 0, but setting this to 1 will return the version number and version date of the copy of sp_CheckHealth you are running, and nothing else.
What do the Importance levels mean?
1 – High. This is the stuff that needs your attention, like recoverability gaps, integrity problems, or configuration that is actively working against you. Try to address these when you can.
2 – Medium. This is the stuff that may or may not be intentional, like certain settings, trace flags, or security configuration. Review these findings to make sure they were all intended or expected.
3 – Low. This is stuff that probably isn’t a problem, but you should be aware of it anyway.
What are the requirements to use sp_CheckHealth?
There are two requirements.
Your SQL Server instance should be using SQL Server 2014 or higher. If you are using an earlier version, execution of the stored procedure will skip some checks because some of the DMVs used don’t exist in earlier versions. The results should still be valid and helpful, but you should really consider upgrading to a newer version. Or maybe it errors out for earlier versions, but if it does we can’t help you other than to say that you should plan to migrate to a supported version.
You need to have VIEW SERVER STATE and VIEW ANY DEFINITION permissions. This tool uses several system tables and DMVs to collect information about your SQL Server instance, and these permissions will allow you to read nearly all necessary information. There are some checks that require permission in the sysadmin role, but those are skipped if this permission is not present.