What should MAXDOP be set to?

Published On: 2018-03-05By:

https://www.flickr.com/photos/126080172@N03/14901447725/

A lot of the examples you’ll see for MAXDOP on the web assume a large server with multiple physical sockets and multiple NUMA nodes.  But if you have a smaller server, like a lot of us do these days, what should you set the MAXDOP to?

The basic rules still apply, that you should set the MAXDOP to 1/2 of the size of your NUMA node. Just because you’re running a smaller server doesn’t mean that you don’t have NUMA configured, it just means that you have a single NUMA node.  So for example if you have a server with one socket and six cores, then your MAXDOP should probably be set to 3; since three is 1/2 the size of the server.

Now there’s a lot of it-depends that goes with this, but this gives you a starting point. You might need to decrease MAXDOP to 2 and see how this affects the server.

Keep in mind that making changes to MAXDOP during the business day isn’t recommended as making changes to MAXDOP will flush the plan cache and cause all the queries to be recompiled.

Denny

The post What should MAXDOP be set to? appeared first on SQL Server with Mr. Denny.

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.