Whats a Good Rule for Max Degree of Parallelism?

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
FROM    sys.dm_os_sys_info

EXEC sys.sp_configure N'max degree of parallelism', @hyperthread_ratio
RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N'show advanced options', '0'
RECONFIGURE WITH OVERRIDE

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.

This entry was posted in Utilities and tagged , , , , , , , . Bookmark the permalink.

5 Responses to Whats a Good Rule for Max Degree of Parallelism?

  1. Great script will test in testing environment.
    Jose Chinchilla
    Twitter: @sqljoe

    • David Levy says:

      Thanks! The most helpful bit if advice I can give is watch the balance between Scheduler Waits and CXPACKET waits. I mention it in the post but really want to call your attention to it because that is the secret as I see it.

  2. karthik says:

    Hi David,

    I just wanted to know what is the relationship between cxpacket and scheduler waits.I know in order tor reduce cxpacket waits, we tend to change max degree of parallelism settings. I did not understand the internals behind your advice.

    Twitter:@karthik_yv

    • David Levy says:

      The easiest way to say it is CXPACKET waits are what you get when you are waiting for your own threads to finish. The SOS Scheduler waits are what you see when your thread(s) are waiting to use the CPU. CXPACKET waits, or parallelism is much more expensive than running single-threaded so you want to avoid it as much as possible with query tuning and by setting max degree of parallelism properly. On the flip-side you also want to avoid not giving your big queries enough CPU to run resulting in scheduler waits and long run times. The idea is to balance the waits with CXPACKET waits slightly higher than SOS SCHEDULER waits.

  3. karthik says:

    Thank you for the explaination.

Leave a Reply

Your email address will not be published. Required fields are marked *