For years, SQL Server’s Maximum Degree of Parallelism (MAXDOP) – the number of cores that a parallel query could use – defaulted to 0, meaning unlimited.
This led to rampant CXPACKET waits because queries went wild with parallelism. Overly wild, like me after six shots of tequila within an hour wild. (It’s not my fault: tequila seems to be my only hangover-free liquor, and when you know you’re not gonna get a hangover…well, buckle up.)
And for years, Microsoft’s knowledge base article 2806535 – the only KB article number I know by heart – basically said set MAXDOP to the number of cores in a processor, up to 8, but no higher than 8. That led to a lot of really awkward discussions around logical vs physical cores (because the KB didn’t say) and how to handle VMs, especially VMs that could move across hosts.
This year, though, Microsoft updated that post to make it a lot more detailed, including different advice for SQL 2008-2014 versus 2016 & newer. Here’s the decision grid for 2016:
This is kind of good news and bad news: the good news is that Microsoft is giving you more details, but the bad news is that your life isn’t getting easier. You still have to deal with the many ways to set & override MAXDOP, figuring out how many NUMA nodes your server has, and monitoring for changes when the sysadmins shut down the server, reconfigure the VMware hosts, and boot you up with a different sockets/cores mix.
Down the road, I look forward to the day when database administrators don’t have to care about this kind of thing because SQL Server sets it by default on startup and adapts it based on the server’s workload. Right now, it’s pretty important, as evidenced by this rather odd line in the KB article:
Each (execution plan) step will use one CPU or the number of CPUs that is specified by MAXDOP and never anything in between.