patternMinor
SQL Server R2 Standard Edition MAXDOP setting
Viewed 0 times
sqlstandardsettingservereditionmaxdop
Problem
I'm looking for confirmation/guidance on setting MAXDOP in a SQL Server instance with the
following config:
Version : SQL Server 2008 R2 Standard Edition
Processor : 2 x AMD Opteron(TM) Processor 6234 = 24 cores
Hyperthreading enabled
For this processor each individual socket has two NUMA nodes, each of which has 6 cores in it. Microsoft’s KB article, Recommendations and guidelines for the “max degree of parallelism” configuration option (http://support.microsoft.com/kb/2806535) recommends that
“For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.” so I would want to set MAXDOP to 6 (the number of cores in one NUMA node.)
However, Microsoft’s documentation on the max degree of parallelism Option http://technet.microsoft.com/en-us/library/ms181007(v=SQL.105).aspx tells me that the maximum value that can be used for MAXDOP in SQL Server 2008 R2 Standard Edition is 4.
So, i’m guessing that the version recommendation of MAXDOP 4 overrides the processor/NUMA recommendation of MAXDOP=6? Anyone else have this configuration and know what happens if I try and set MAXDOP to 6 regardless?
following config:
Version : SQL Server 2008 R2 Standard Edition
Processor : 2 x AMD Opteron(TM) Processor 6234 = 24 cores
Hyperthreading enabled
For this processor each individual socket has two NUMA nodes, each of which has 6 cores in it. Microsoft’s KB article, Recommendations and guidelines for the “max degree of parallelism” configuration option (http://support.microsoft.com/kb/2806535) recommends that
“For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.” so I would want to set MAXDOP to 6 (the number of cores in one NUMA node.)
However, Microsoft’s documentation on the max degree of parallelism Option http://technet.microsoft.com/en-us/library/ms181007(v=SQL.105).aspx tells me that the maximum value that can be used for MAXDOP in SQL Server 2008 R2 Standard Edition is 4.
So, i’m guessing that the version recommendation of MAXDOP 4 overrides the processor/NUMA recommendation of MAXDOP=6? Anyone else have this configuration and know what happens if I try and set MAXDOP to 6 regardless?
Solution
Standard edition will not generate a parallel plan that uses more than 4 processors i.e.
MAXDOP 4. Setting MAXDOP to a value higher than this will not alter the limitation imposed by the edition. You get no warning or error when setting the higher value, it will be accepted.Context
StackExchange Database Administrators Q#54434, answer score: 7
Revisions (0)
No revisions yet.