Recommended MAXDOP setting

Question

Recommended MAXDOP setting

2Fire

SSC Veteran

Points: 279
More actions
August 27, 2014 at 12:59 pm

#310491

We are running SQL2012 with -
2 physical processors
6 cores per processor
24 logical processors
NUMA enabled - 2 nodes and 12 logical processors/node

Per MSKB article - http://support.microsoft.com/kb/2806535

•For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.

•For servers that have hyperthreading enabled, the MAXDOP value should not exceed the number of physical processors.

•For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.

This would indicate MAXDOP = 1.

Per a health audit performed on our SQL2008 server MAXDOP for NUMA architecture systems is to the number of physical cores in a single NUMA node.

That would indicate MAXDOP = 6.

I am confused! Can anyone recommend an appropriate MAXDOP setting for our configuration?

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

2Fire (8/27/2014)
We are running SQL2012 with -
2 physical processors
6 cores per processor
24 logical processors
NUMA enabled - 2 nodes and 12 logical processors/node
Per MSKB article - http://support.microsoft.com/kb/2806535
•For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
•For servers that have hyperthreading enabled, the MAXDOP value should not exceed the number of physical processors.
•For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.
This would indicate MAXDOP = 1.
Per a health audit performed on our SQL2008 server MAXDOP for NUMA architecture systems is to the number of physical cores in a single NUMA node.
That would indicate MAXDOP = 6.
I am confused! Can anyone recommend an appropriate MAXDOP setting for our configuration?

Quick thought, although MAXDOP does what it says on the tin (more or less), a better approach is to look at the cost threshold. There have been quite few drastic changes in the last two versions of SQL Server, almost prompts me to say "forget about the old stuff (2K8 and earlier)" kind of things.

😎

sqldriver SSChampion Points: 10440 More actions · Answer 2

Eirikur Eiriksson (8/27/2014)
2Fire (8/27/2014)
We are running SQL2012 with -
2 physical processors
6 cores per processor
24 logical processors
NUMA enabled - 2 nodes and 12 logical processors/node
Per MSKB article - http://support.microsoft.com/kb/2806535
•For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
•For servers that have hyperthreading enabled, the MAXDOP value should not exceed the number of physical processors.
•For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.
This would indicate MAXDOP = 1.
Per a health audit performed on our SQL2008 server MAXDOP for NUMA architecture systems is to the number of physical cores in a single NUMA node.
That would indicate MAXDOP = 6.
I am confused! Can anyone recommend an appropriate MAXDOP setting for our configuration?
Quick thought, although MAXDOP does what it says on the tin (more or less), a better approach is to look at the cost threshold. There have been quite few drastic changes in the last two versions of SQL Server, almost prompts me to say "forget about the old stuff (2K8 and earlier)" kind of things.
😎

Eirikur is right. MAXDOP is helpful, but you should know what cost thresholds your queries are hitting before determining how many processors you need them hitting. I found some fairly helpful calculations here, when I was trying to tune things on about half a dozen new servers in my environment. You can set MAXDOP to whatever you want, but if your queries are either all under or all over your CTFP, it likely won't benefit you much.

Thanks

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

I would go with 6 - the number of PHYSICAL cores per NUMA node.

As other's said, Cost Threshold for Parallelism is VERY important. 5 is a universally too-low number these days.

Also note that Linchi Shea and Adam Machanic have done testing on some systems to show that > NUMA MAXDOP can be more efficient in some cases. Test, test, test! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

2Fire SSC Veteran Points: 279 More actions · Answer 4

Thanks all. I appreciate the quick and helpful replies. Especially regarding Cost Threshold for Parallelism.

I ran the calculations referenced and increased CTFP per results. If you have any recommendations for additional reading on this subject, please let me know.

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 5

All, nice answers.

I think this is the final word on the correct and appropriate setting for MAXDOP: http://support.microsoft.com/en-us/kb/2806535

VishnuGupthanSQLPowershellDBA Ten Centuries Points: 1230 More actions · Answer 6

TheSQLGuru - Wednesday, August 27, 2014 3:15 PM
I would go with 6 - the number of PHYSICAL cores per NUMA node. As other's said, Cost Threshold for Parallelism is VERY important. 5 is a universally too-low number these days.Also note that Linchi Shea and Adam Machanic have done testing on some systems to show that > NUMA MAXDOP can be more efficient in some cases. Test, test, test! 🙂

Hi Kevin,
Just to clarify, MS site is suggesting in terms of Logical processors as shown below ryt ?. In that case we can go with 8 also , as the Logical processors said in the example is 12.
https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi 

Server with multiple NUMA nodes

Greater than 8 logical processors per NUMA node

Keep MAXDOP at 8

Please correct if am wrong.

Vishnu

Vishnu Gupthan
PowershellAcademy
www.powershellacademy.com