Max Degree of Parallelism Server-Side Setting

Mon Nov 8, 2010 by Tara Kizer in sql-server-database-administration

Recently I opened a case with Microsoft PSS to help us through a severe performance problem on a new system. As part of that case, the PSS engineer checked our “max degree of parallelism” server-side setting. It is our standard to use 4 on our production systems that have 16 CPUs (2 sockets, quad-core, hyper-threaded). The PSS engineer had me run the below query to get Microsoft’s recommended value of “max degree of parallelism” server-side setting for our 16-CPU system:

select 
    case 
        when cpu_count / hyperthread_ratio > 8 then 8
        else cpu_count / hyperthread_ratio
    end as optimal_maxdop_setting
from sys.dm_os_sys_info;

The query returned 2. I made the change using sp_configure, and it did not resolve our issue. We have decided to leave it in place for now.

Do you agree with this query? What are your thoughts on this?

If you decide to change your setting to reflect the output of this query, please test it first to ensure there are no negative side effects.

Legacy Comments


Chad 2010-11-11	re: Max Degree of Parallelism Server-Side Setting No, in fact the engine team that originally posted this query in their blog has amended the post as follows: "Recently, I came across a limitation in this system view. The hyperthread_ratio column is > 0 for the multi-core systems too. So there is no way to differentiate between a system with multi-core and hyperthreaded CPUs using the DMV. And since in the case of multi-core processor based systems, the MAXDOP value can be set to the number of CPUs the usefulness of the query is limited. The sure way to know the effect of MAXDOP setting is to test against your workload that involves parallel queries/operations." blogs.msdn.com/.../466387.aspx Another thingt this query ignores is NUMA. If NUMA is being used, you want to set MAXDOP to the number of cores per NUMA node. According to Best Practice, your optimal setting should actually be 8.

Chad 2010-11-11	re: Max Degree of Parallelism Server-Side Setting 8 Assuming non NUMA I should say.

eyechart 2010-11-11	re: Max Degree of Parallelism Server-Side Setting Who is using NUMA? that would be a good survey. wrt MAXDOP I have found that anything above 4 leads to headaches in a system that is primarily OLTP on a system with more than 16 cores. for BI or reporting environments allowing more parallism might be beneficial. Too bad this DMV isn't actually accurate. kind of typical of Microsoft though. They deliver something that is almost right but never fix it.

Chad 2010-11-11	re: Max Degree of Parallelism Server-Side Setting A lot of SQL Servers are on NUMA hardware. I would say > 50% of the customers I go to are using NUMA.

Frank 2010-11-11	re: Max Degree of Parallelism Server-Side Setting I set the maxdop to 1 in my OLTP database,I think I do not need it.And there are not the CXPACKET wait.