I’m starting off a set of posts regarding settings I often adjust, what they do and suggested values for them when dealing with quite ordinary OLTP systems. In this first writeup I am starting with the very basic, but very important, Max Degree of Parallelism (MAXDOP) and Cost Threshold for Parallelism (CTFP). The primary reason why I’m choosing to start with these two in particular is that I have yet to work on a system where the defaults where appropriate for production use, hence they always need adjusting.
MAXDOP
For the purpose and scope of this post I will loosely define MAXDOP as a setting that controls how many active threads SQL server is allowed to use concurrently. This is the equivalent of controlling how many cores (aka schedulers) SQL server can use to service a request. For a more in depth read and a more stringent explanation, I have linked some excellent resources on the matter at the bottom of the post.
Microsoft discloses in their official documentation that the default value for MAXDOP “is not the recommended value for most cases”. Why is that? Well, the default setting for MAXDOP is 0. According to the official documentation that means SQL server will use “all the available processors up to 64 processors”.
Microsoft has official recommendations expressed as guidelines on how to set MAXDOP appropriately for most scenarios. For most newer systems, it boils down to 8 in my experience. The breakdown is as follows:
| 1 NUMA node | 1 NUMA node | several NUMA nodes | several NUMA nodes |
| <= 8 cores | > 8 cores | <=16 cores per node | > 16 cores per node |
| < = # of cores | 8 | < = # of cores per node | ½ of cores per node, max 16 |
CTFP
When SQL Server compiles a query, it calculates a cost related to its execution. If the calculated cost is equal to or larger than CTFP, SQL server can consider a parallel plan (there are exceptions where the cost can be lower, but they are not relevant here). The default value for CTFP is 5, which Microsoft claims to be “adequate for most systems”. I respectfully disagree. In my experience, it is too low and will cause way to many queries to go parallel.
My thoughts on the matter
MAXDOP and CTFP play together to form a ruleset that SQL server must stay within when considering parallel execution for a request. I see no reason to not follow the recommended approach from Microsoft regarding MAXDOP, however I simplify it to be deterministic also for 8 cores or below and set it to the number of cores for servers with 8 cores or less. For CTFP I usually set it to 50 as a starting point and it has served me well so far. Both values might need adjusting dependent on the workload.
Suggested reading for more information:
A good writeup from Pedro Lopez at Microsoft
Paul White also has some good information on the matter here.
Some very informative information on Thread and Task Architecture from the official documentation.
And, of course, the official documentation from Microsoft on MAXDOP and CTFP