Max Degree of Parallelism Server-Side Setting
- by Tara Kizer
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.
