Fix SQL Server with one click

Tempting headline, isn't it? It might even be considered click-bait, but that's not the idea.

The SQL Server default configuration is not recommended for production environments, and yet I have worked on many production environments that have been set up by people who don't know that the default configurations are not recommended. These same people shouldn't have to care either, because their job is to install SQL Server so that the vendor product they've purchased can work as required.

You know the type. They get to this screen, click "Select All", and then click "Next".

(Image from sqlcoffee.com)

I've been working on a tool written in C#, using .NET Framework 4.7.2, that can fix the most common issues I see with a default SQL Server installation. The intention is to change the fewest number of settings for the most gain.

If you know SQL Server, this tool is not for you. In fact, this tool is for all the folks who know nothing about SQL Server and want to set and forget it.

Data professionals know that there's no Easy Button to make SQL Server configured perfectly on every server. That's why we use configuration files, T-SQL scripts, and other methods to make sure that our SQL Server instances work well on our environments.

Alas, we are in the minority. Until that changes and every company moves to Azure SQL Database, we have to stick with the defaults that come inside the box.

So with that in mind, I am developing a tool that will do the following:

• Set cost threshold for parallelism to 50
• Disable lightweight pooling if it is enabled
• Disable priority boost if it is enabled
• Set optimize for ad hoc workloads to enabled
• Set max server memory (MB) to a custom value consistent with Jonathan Kehayias's algorithm
• Set backup compression default to enabled
• Set the power saving settings on Windows to high performance if possible
• Provide an option to flush the plan cache as needed

What this tool does not do, is change the max degree of parallelism (MAXDOP). Nor does it fix file growth and autogrowth settings. It does not set tempdb file sizes. It does not do any of the other cool things that you do as a database consultant or DBA. That's not the intention, because that's your job.

This tool is designed for the folks out there who are beholden to vendor products that make use of SQL Server, but cannot change anything. The default settings in SQL Server affected by this tool will at the very least help those lovely people have some sort of starting point before engaging with expensive consultants.

What's more, it doesn't require any SQL Server downtime. That's value you can't buy.

Let me know which default configuration setting you change first, in the comments below.