Check SQL Server Configuration – Free Script

I do a lot of SQL Server Health Assessments on SQL Server environments. I’m always checking  sys.configurations to look for problematic instance level configuration settings. I almost always find configurations worth changing. But the approach I’ve always used is a bit manual and I wanted to fix it.  So I finally wrote a script to save me some time and filter out some of the noise of defaults that I’m not looking at unless there is a specific issue. Maybe this script or the links can help when you need to check SQL Server configuration options.

Check SQL Server Configuration Values Yourself

The way I normally do it is something like  select all of the values (select * from sys.configurations) and scroll for the ones I really care about. Looking carefully for things change from defaults that could lead to questions for me to ask my client, or bad defaults left selected. Some of these findings can lead to reliability or performance concerns for my client. I want to quickly find them.

So I set out to write a script finally. And the first version of that script can be downloaded here (Version 0.1 – a bit of a beta –  for now, will change the insert statement approach, include support for SQL Server 2005 and perhaps 2000 in a coming update as well as any other changes necessary). It works in SQL Server 2008 and up.

SSMS Reports – A source for information about SQL Configurations

SQL Server Management Studio as you may, or may not know has some reports built in and has for a few versions now. Some good reports with good info. One of them is the “Server Dashboard Report”

So I ran this report with SQL Server profiler up in one of my VMs, watched the queries run to grab the data and used those queries and the books online articles about default configuration values to build the script that does the checking.On this report is a “Non-Default Configuration Options” section. This section lists configuration options that were changed from the default. I’ve noticed it misses a couple in some versions, but it is a good concept.

You should play with your SSMS reports if you haven’t, and have a look at the code they spit out, I wouldn’t replicate their code for everything in a lot of situations, but it is a good way to see how the reports are generated.

Simple Output/Ugly Script For SQL Server Configuration Check

The script is long and a bit ugly, admittedly. It checks the version of SQL you are running, and then either exits if it isn’t 2008, 2008R2 or 2012. If it is one of those, it uses simple IF logic to get to that section and builds a couple table variables of default configuration options, and problematic default configuration options and then checks your configuration options. Once it has your instances configurations, it compares against the defaults and then compares against the POTENTIALLY bad defaults and spits out a simple text output with the findings.

The SP_Configure Findings Output

Three main categories of findings are released:

In Use Value Differs From Set/Running Values

If you’ve set one value from the SSMS GUI or from sp_configure but that value is not the currently running value you’ll see that in the report. This is a good sign that you either have to run RECONFIGURE or restart SQL Server for that setting to take place. You can use the sys.configurations table to see which settings are able to be changed on the fly, and which require a restart. I go through this on my blog post about sys.configurations and how to use sp_configure.

In Use Value Differs From Default Configuration Option

This isn’t necessarily a bad sign, but it quickly shows you where a configuration option was changed through the GUI or sp_configure from the default for that edition of SQL Server. These are worth looking at and investigating to make sure you at least know why they were changed, and to look for any signs of trouble (I still bump into environments with Priority Boost enabled because they think that solves performance problems during checks)

Potentially Bad Default Selected

The word potential is important here. Your mileage may vary in your environment. In upcoming blog posts in the SP_Configure blog category here we will blog about each of these and why we check for them, and what we recommend and why. That said the list of what we are looking for with a couple notes are below. Some of the best practices for these are either ranges, calculations or “it depends” scenarios so don’t go changing all of these just because they are here (perhaps Max Server Memory is one exception, maybe Optimize For Ad Hoc Workloads  and perhaps backup compression are exceptions, but even these have some minor caveats).

Configurations Checked here:

• Backup Compression Default – We like to typically see this set to 1
• Max Degree of Parallelism – There is no one size fits all guidance, but we like to know when it is default (and when it was changed, but the second check picks that one up.) Microsoft has some guidance that is mostly a good guide here.
• Max Server Memory – We like to see memory purposefully left for the OS. How much? It depends on what else is running on the server, but somewhere around 20% is where we typically start the “bidding”.
• Min Server Memory – Perhaps not as important as Max Server Memory, but we like to know if it is default and will sometimes suggest changes.
• Optimize For Ad Hoc Workloads – This can tend to be set to 1. There are some very rare exceptions, but really a lot of guidance suggest the default of this could be 1 and things would be okay.
• Remote Admin Connections – The default is Off (0) here. I like it on  (1) so I can connect to a client’s instance with a remote administrator connection without having to restart. Helps in the rarest of rare situations but it is a nice tool to have.
• Cost Threshold For Parallelism – 5 worked awhile back when this first came out. It likely doesn’t know in most environments. The right number takes some work to get to, but we like to look for the default and normally bump that up as you’ll see in a coming post.

Check Your Configurations!

Enjoy the script, try it out and let us know if you have any issues or any defaults that you like to check as well and we’ll make improvements and consider changes in a future release. Look into your SQL Server configurations and make sure you are following best practices.

See where you are off and make appropriate changes to your environment to help get on track with Database HEALTH!