sp_health_check quickly outlines key facts about the status and specs of a Microsoft SQL Server which gives a starting point to address current and potential issues.

Despite the fact that there are multiple queries executed by the stored procedure, the data in question is mainly retrieved from memory, so it is safe to be run in production. It contains a waitfor delay, which extends the elapsed time. Try it first on a testing environment.

The stored procedure is a compound of scripts that were not only created by me; more specifically, there’s code from (in order of appearance):

And although I did the rest, I stand on the shoulders of giants such as Jonathan Kehayias, Paul Randall, Benjamin Nevarez, Aaron Bertrand, Kendra Little, David Klee, Kevin Kline, and other authors to whom I owe a lot of what I know.

sp_health_check is functional on all recent versions of SQL Server, and will display more useful information on versions starting from 2008 R2 SP1.

The login executing sp_health_check requires sysadmin privileges.

The stored procedure uses the extended stored procedure xp_cmdshell to query the Windows WMIC when the data is unavailable on dynamic management views on old versions.

A list of what the script checks is being put together and will be published shortly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s