The title sounds like something that you would see for a boxing match. However, in reality it is a misleading title. The Max Degree of Parallelism and the Cost Threshold for Parallelism SQL Server settings actually work more together than they do against each other.
These two settings actually define the how many and the when in regards to parallel execution plans. The Max Degree of Parallelism (MDop) simply defines the number of processors/cores that SQL Server will use when the optimizer determines parallelism is needed. The Cost Threshold for Parallelism is cost threshold of when the SQL Server will use parallelism. The cost is the overall cost the optimizer determines for each query and SQL Server will use parallelism if the cost is above the threshold value.
The recommended settings for MDop is the number of cores not to exceed 8. However, when setting this, it is important to continue to monitor the system to see if the change has caused an improvement. The default value is 0.
The recommended setting for the Cost Threshold for Parallelism is 25 to 50 and has a default value of 5. Yes, the default setting is too low. By changing this, SQL Server will reduce the number of smaller queries that may use parallelism. Just like the MDop setting, it is important to monitor the server after making a change to see if an improvement is make. There have been many times where simply changing this setting from the default, I have seen the CPU utilization drop from close to 100% to less than 10%. This in no way means you will see the same improvement, just what I have seen.
Neither one of these settings require a reboot of the service when changing. It is also important to keep in mind that if the MDop is set to 1, SQL Server will ignore the Cost Threshold for Parallelism setting.
This snippet of code can be used to query server configuration settings
- SELECT CONVERT(VARCHAR(60), name) AS 'Name'
- , value
- , description
- FROM sys.configurations
Thanks for reading!!!!!