Home
%3CLINGO-SUB%20id%3D%22lingo-sub-780071%22%20slang%3D%22en-US%22%3EMaxDOP%20Added%20to%20SQL%202019%20CTP3.0%20Setup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780071%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20now%20set%20MaxDOP%20during%20SQL%202019%20CTP3.0%20setup.%20Max%20Degree%20Of%20Parallelism%20is%20the%20sledge%20hammer%20to%20Cost%20Threshold's%20tack%20hammer%20(also%20MaxDOP%20database%20scoped%20configuration%20in%20SQL%202017%2B)%20and%20getting%20MaxDOP%20set%20correctly%20can%20be%20an%20art%20that%20is%20highly%20dependent%20on%20your%20workload.%20However%2C%20there%20are%20some%20best%20practice%20guidelines%20we%20can%20follow.%20Remember%20that%20best%20practices%20are%20just%20a%20starting%20place%20and%20further%20tuning%20may%20be%20needed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EBest%20Practice%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThe%20general%20best%20practice%20for%20MaxDOP%20is%20to%20stay%20within%20a%20NUMA%20node.%20This%20allows%20us%20to%20avoid%20using%20remote%20memory%20and%20use%20memory%20that%20is%20local%20to%20that%20physical%20processor.%20This%20is%20pretty%20easy%20to%20figure%20out%20with%20just%20hardware%20based%20NUMA%2C%20but%20in%20SQL%202016%20software%20based%20NUMA%20was%20added.%20The%20same%20rule%20applies%20though%2C%20we%20need%20to%20set%20MaxDOP%20to%20stay%20within%20a%20soft%20NUMA%20node.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EGuidelines%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhat%20are%20the%20defaults%20for%20MaxDOP%20during%20SQL%202019%20CTP3.0%20setup%3F%20Below%20is%20the%20quick%20guide%20matrix%2C%20but%20you%20should%20really%20read%20our%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F2806535%2Frecommendations-and-guidelines-for-the-max-degree-of-parallelism-confi%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGuidelines%20for%20Setting%20MaxDOP%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESQL%20Server%202016%2B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CTABLE%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EServer%20with%20single%20NUMA%20node%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ELess%20than%20or%20equal%20to%208%20logical%20processors%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EKeep%20MAXDOP%20at%20or%20below%20%23%20of%20logical%20processors%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EServer%20with%20single%20NUMA%20node%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EGreater%20than%208%20logical%20processors%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EKeep%20MAXDOP%20at%26nbsp%3B8%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EServer%20with%20multiple%20NUMA%20nodes%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ELess%20than%20or%20equal%20to%2016%20logical%20processors%20per%20NUMA%20node%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EKeep%20MAXDOP%20at%20or%20below%20%23%20of%20logical%20processors%20per%20NUMA%20node%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EServer%20with%20multiple%20NUMA%20nodes%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EGreater%20than%2016%20logical%20processors%20per%20NUMA%20node%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EKeep%20MAXDOP%20at%26nbsp%3Bhalf%20the%26nbsp%3Bnumber%20of%20logical%20processors%20per%20NUMA%20node%26nbsp%3Bwith%20a%20MAX%20value%20of%2016%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESQL%20Server%202008%20to%202014%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CTABLE%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EServer%20with%20single%20NUMA%20node%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ELess%20than%20or%20equal%20to%208%20logical%20processes%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EKeep%20MAXDOP%20at%20or%20below%20%23%20of%20logical%20processors%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EServer%20with%20single%20NUMA%20node%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EGreater%20than%208%20logical%20processors%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EKeep%20MAXDOP%20at%26nbsp%3B8%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EServer%20with%20multiple%20NUMA%20nodes%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3ELess%20than%20or%20equal%20to%208%20logical%20processors%20per%20NUMA%20node%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EKeep%20MAXDOP%20at%20or%20below%20%23%20of%20logical%20processors%20per%20NUMA%20node%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%3CP%3EServer%20with%20multiple%20NUMA%20nodes%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EGreater%20than%208%20logical%20processors%20per%20NUMA%20node%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%3E%3CP%3EKeep%20MAXDOP%20at%26nbsp%3B8%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EExamples%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EAlthough%20these%20examples%20are%20all%20from%20the%20GUI%20to%20show%20the%20chosen%20defaults%2C%20note%20that%20you%20can%20also%20change%20the%20defaults%20for%20command%20line%20and%20unattended%20installs%20using%20the%20%3CSTRONG%3E%2FSQLMAXDOP%3D%20%3C%2FSTRONG%3Eparameter.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20765px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125009i96A1F59E7CF5E935%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22blog%20image.png%22%20title%3D%22blog%20image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-780071%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20now%20set%20MaxDOP%20during%20SQL%202019%20CTP3.0%20setup.%20We%20take%20a%20look%20at%20the%20default%20values%20and%20how%20those%20are%20derived%20from%20logical%20processors%20and%20NUMA.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-780071%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ERyanAdams%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

You can now set MaxDOP during SQL 2019 CTP3.0 setup. Max Degree Of Parallelism is the sledge hammer to Cost Threshold's tack hammer (also MaxDOP database scoped configuration in SQL 2017+) and getting MaxDOP set correctly can be an art that is highly dependent on your workload. However, there are some best practice guidelines we can follow. Remember that best practices are just a starting place and further tuning may be needed.

 

Best Practice

The general best practice for MaxDOP is to stay within a NUMA node. This allows us to avoid using remote memory and use memory that is local to that physical processor. This is pretty easy to figure out with just hardware based NUMA, but in SQL 2016 software based NUMA was added. The same rule applies though, we need to set MaxDOP to stay within a soft NUMA node.

 

Guidelines

What are the defaults for MaxDOP during SQL 2019 CTP3.0 setup? Below is the quick guide matrix, but you should really read our Guidelines for Setting MaxDOP.

 

SQL Server 2016+

Server with single NUMA node

Less than or equal to 8 logical processors

Keep MAXDOP at or below # of logical processors

Server with single NUMA node

Greater than 8 logical processors

Keep MAXDOP at 8

Server with multiple NUMA nodes

Less than or equal to 16 logical processors per NUMA node

Keep MAXDOP at or below # of logical processors per NUMA node

Server with multiple NUMA nodes

Greater than 16 logical processors per NUMA node

Keep MAXDOP at half the number of logical processors per NUMA node with a MAX value of 16 

 

SQL Server 2008 to 2014

Server with single NUMA node

Less than or equal to 8 logical processes

Keep MAXDOP at or below # of logical processors

Server with single NUMA node 

Greater than 8 logical processors

Keep MAXDOP at 8

Server with multiple NUMA nodes

Less than or equal to 8 logical processors per NUMA node

Keep MAXDOP at or below # of logical processors per NUMA node

Server with multiple NUMA nodes

Greater than 8 logical processors per NUMA node

Keep MAXDOP at 8

 

Examples

Although these examples are all from the GUI to show the chosen defaults, note that you can also change the defaults for command line and unattended installs using the /SQLMAXDOP= parameter.

 

blog image.png