Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Monday, November 08, 2010

Max Degree of Parallelism Server-Side Setting

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.

posted @ Monday, November 08, 2010 12:47 PM | Feedback (5) | Filed Under [ SQL Server - Database Administration ]

Powered by:
Powered By Subtext Powered By ASP.NET