(This post was updated on 5 February 2022.)
Tempting headline, isn’t it? It might even seem like clickbait, but that’s not the intention.
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 may have seen this before. They get to this screen, click “Select All”, and then click “Next”.
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. This tool is intended for all the folks who know nothing about SQL Server and want to set it and forget it.
Data professionals know that there’s no “Easy Button” to configure SQL Server 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 in 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’m doing some outside-the-box thinking for what’s inside the box, 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 (read Don’t optimize for ad hoc workloads as a best practice)
- 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 or do not 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.
Which one do I change first? That’s a tough one, as I change them all. The order really depends on where Microsoft put the setting ????
Very cool tool, though. I can think of four companies that could use something like this on a regular basis.
Great blog. Years later, it has still helped me.
ok, silly question, but where is the tool? I can’t find any download option
It hasn’t been released. As I noted right in the beginning, I said “I’m working on a tool”. The post was more a discussion about these settings and eliciting feedback.