So are some of mine.
So are 90% of the servers I run across, especially in mid-size businesses ( <1000 employees, or 10-20 servers typically).
Its probably not your fault.
There are a bunch of instance level settings in SQL Server that need to be set that are specific to your environment and the workload you are going to run against it. Most of the defaults that are in the SQL Server installer are set up to make sure the performance is acceptable on a very modest server. Until version 2016, the installer had not changed much.
The problem is that in a large number of organizations, the person doing the installation of SQL Server is a Developer, or a SysAdmin running a script. Both are great at their core jobs, but does either of them know what Cost Threshold for Parallelism is? It is not in the installer…it has to be configured post install or left at a very old default number that most likely makes no sense for you.
Likewise, if you are running SQL Server installed from a script that came with a 3rd party software product, you very likely have the “safest” possible settings in place.
Some of the most common things I see everywhere:
- Instant File Initialization not enabled
- Cost Threshold for Parallelism not set correctly
- MAXDOP set to 1 or ALL CPUs
- MAX Memory left at default
- MAX Memory left at default on a multi-instance server
- Default file locations for system databases, or all
- No maintenance processes
That is the short list.
Sales Pitch: I will give you the first hour (one server) free to check on your configuration. This involves me running a set of scripts, analyzing the results and sending back some recommendations. Yes, I want to implement them for you as well.
Alternatively, a lighter weight version of this is the Constant Care offering from Brent Ozar Unlimited. I can take his results and explain what they mean so that we can make intelligent decisions together. We are not affiliated with Brent Ozar Unlimited, other than as part of the SQL Community.
Let’s talk. Click HERE to set a meeting on my Calendar.
Thanks for reading!