Introduction
I am one of those people that believe that anything worth doing is worth having a script to do it. Setting the Max Degree of Parallelism is no exception. In this post I will go through the script I use as well as the metrics I watch to make sure the setting is correct.
Reasoning
Max Degree of Parallelism (MAXDOP) is a setting that many people do not get right and for good reason it is tricky. The easiest scenario is a server with NUMA architecture. For NUMA servers the stepping off point is the number of cores per NUMA node. For other servers the answer is often “It Depends” followed by “It Changes Over Time”. The good news here is that the setting is fairly easy to tune by watching wait stats.
The rule of thumb I use for gauging whether or not my settings are correct is to make sure that CXPACKET waits are slightly higher than SOS_SCHEDULER_YIELD. My goal is to get them as close as possible to each other without SOS_SCHEDULER_YIELD waits exceeding CXPACKET waits. Depending on your workload and the amount of tuning you are doing you may need to evaluate your settings more often.
The Script
This script is dirt simple but do not be deceived, this is not a change to take lightly. It is a great starting off point for a new server but careful research should be done before running on any existing production systems. The basic logic is enable ‘show advanced options’ then get the hyperthread_ratio from sys.dm_os_sys_info. This will give you the number of cores per socket and that is typically equal to the number of cores on a NUMA node. Hyperthreading is not the same as multiple cores, I am not sure I would use this script on a hyperthreaded server. Finally the script sets ‘max degree of parallelism’ to the hyperthread ratio and turns off ‘show advanced options’.
1 2 3 4 5 6 7 8 9 10 11 12 13 | EXEC sys.sp_configure N'show advanced options', '1' RECONFIGURE WITH OVERRIDE DECLARE @hyperthread_ratio INT SELECT @hyperthread_ratio = hyperthread_ratio EXEC sys.sp_configure N'max degree of parallelism', @hyperthread_ratio EXEC sys.sp_configure N'show advanced options', '0' |
Conclusion
As usual, I hope you find this script helpful. This script and the basic rules I have laid out work well for me in my environment. Test it carefully in yours. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
