Brent Ozar
Production Database Administration
22 Comments
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 , 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.
22 Comments. Leave new
Johannes Vink
December 16, 2019 11:11 am
ReplyZilch advise on Azure SQL DB where we can dynamically scale… I’ve encountered once that I had to fiddle with the MAXDOP setting as the query itself did not want to cooperate.
the whole article is full of ‘check your worload’ and ‘could be different for you’. Short summary: 8. Always 8. Unless you hit one of the exceptions listed, then it is different. Argh.
Started to wonder how to check what kind of DOP settings are used when running with MAXDOP = 0… according to the docs sys.dm_exec_query_stats returns some information on that… hmmm.
Brent Ozar
December 16, 2019 11:12 am
ReplyIn fairness, the workloads really are different – there’s no one biblical answer for everyone.
To see information about current & past parallelism for a query, check out sp_BlitzWho @ExpertMode = 1, and you can also dig into the source code on that to see where Erik got that data from.
John Zabroski
December 16, 2019 12:11 pm
*cough* use Snowflake *cough*
Reply
Steve
December 16, 2019 11:57 am
ReplyMy issue is what to do with small SQL servers with only 4 cores.
Do I set to 4 (or 0) and use all cores, or set to 2 and maybe allow some concurrency but with potentially slow big queries as the trade off
My usual hope is that if the server only need 4 cores then the workload is light enough for it not to matter at all
Brent Ozar
December 16, 2019 1:13 pm
If you only have 4 cores and you’re worried about the speed of big queries, well, yeah, I think you’ve got your answer right there, and it has nothing to do with MAXDOP. 😉
ReplyGerald
January 22, 2020 6:33 am
I was wondering the same thing (for local/laptop development environment). With multiple instances (that talk to each other via linked server) and limited overall resources, do I set to 4 for each instance or 2 so they can both work at the same time (although with limited, shared RAM I’m not sure how much MAXDOP is going to matter when running concurrent processes on separate instances)…
Reply
kapsiR
December 16, 2019 1:02 pm
ReplyInteresting, specifies a bit about MAXDOP 0 and single processor etc.
When you think about it, SQL Server has all the information required to dynamically set MAXDOP. This should be a configurable setting “Automatically adjust MAXDOP to suit workload”
ReplyJohn Zabroski
December 16, 2019 2:21 pm
Reply…But then SQL Server Engineering Support has to have customers specify whether they’ve flipped this flag, etc. And they have to worry about whether all environment edge cases like running in a Windows Server Hyper-V guest has falsely presented the processor info, and whether the BIOS firmware is up to date on the Host OS, etc. And what if some developer enabled MSDTC?
There’s really no replacement for a tool like SolarWinds Database Performance Analyzer or SQL Sentry. You have to continuously monitor your wait types and adjust accordingly.
Mitchell Wheat
December 16, 2019 2:28 pm
Reply“There’s really no replacement for a tool like SolarWinds Database Performance Analyzer or SQL Sentry” – or a good DBA! Simply throwing tools at a problem, means you have another problem.
I’d be happy with a configurable setting to dynamically adjust MAXDOP, After all, you don’t have to turn it on. It could add a suitable warning to the error logs, and I’m sure MS could add the check to any of the support engineer tooling.
Andrew Tobin
December 16, 2019 10:44 pm
I do wonder when we say adjust it to suit workload – if it became the workload of the server, or the workload of the query. If it was to figure out how many querybucks the plans were and then adjusted the maxdop to a number that made sense for the estimated rows and stored query plan then it’s a new parameter sniffing issue. That might not be a terrible thing if it was working akin to memory grants and if it could take recompile hints or similar… but it’s an interesting debate to have.
Reply
JRStern
December 16, 2019 4:52 pm
Seems inconsistent, wonder if there’s some reason. Why limit to 8 on single NUMA node, but up to 16 per NUMA node if you have multiple NUMA nodes? I suspect it’s because, if you’re lucky enough to have 64++ cores all licensed and everything, you may not have enough NUMA nodes otherwise. What’s the highest NUMA node count anyone has seen?
ReplyMitchell Wheat
December 16, 2019 4:56 pm
Highest NUMA node count I’ve personally seen is 4 nodes of 12 cores each, but that’s probably small beer….
ReplyJRStern
December 16, 2019 5:04 pm
I briefly used a couple of monster machines with 128 and 256 cores, but never got to mess much with MAXDOP, can’t recall if we ever moved it from zero, no idea of NUMA. Would have been SQL 2008 R2, I think. FWIW. That was before per-core licensing. Since then, not so much.
Reply
JRStern
December 16, 2019 5:01 pm
Aha. Just actually clicked on the article. Below the chart it notes these are for the “soft NUMA” that is set automagically if hard NUMA is not detected. Does that matter? Is that documentation on soft NUMA new? Also, “Each step will use one CPU or the number of CPUs that is specified by MAXDOP and never anything in between. If multiple operators of a plan execute concurrently in parallel, the total number of threads that are used by the query may exceed the MAXDOP setting specified”, is that new? The “never in-between” is news to me, but I’ve wondered about it for years and guessed that it was the case.
ReplyAndre
December 17, 2019 2:00 am
Wait what? Are you serious about the Tequila? I will need to try it out 🙂
ReplyBrent Ozar
December 17, 2019 5:25 am
Yeah! No mixers, no sugar, just straight good tequila like Clasé Azul. Works beautifully for me.
ReplyJohn Zabroski
December 17, 2019 5:44 am
Must’ve discovered that fact in college. That’s when most of us stop drinking tequila. Avion is underpriced relative to quality, in my experience. Their Espresso Tequila is fantastic in coffee. But I don’t drink much.
ReplyBrent Ozar
December 17, 2019 5:51 am
Hahaha, no, other way around. I only started drinking tequila in my 30s – I was a beer & wine & flavored-liquor guy before that. By the time I finally tried tequila, I could afford the good stuff, hahaha.
ReplyJohn Zabroski
December 17, 2019 6:05 am
Double up the smoothness of your favorite tequila points by pouring your SO a shot and saying, “Alexa, play You and Tequila by Kenny Chesney”
Gerald
January 22, 2020 6:41 am
I (like most I am guessing) had the bad experience with tequila that put it off of my list for a long number of years… The new(ish) gig always has a bottle of Casamigos here so I’ve revisited after 10 years or so on the NO list and it turns out the mixers/sugar/etc. (and/or the cheap/bad tequila) were DEFINITELY the problem.
Reply
Pat
December 19, 2019 11:57 am
Is there any guidance for setting it on SQL Azure databases?
Reply
Leave a Reply
Hi! I’m Brent Ozar.
I make Microsoft SQL Server go faster. I love teaching, travel, cars, and laughing. I’m based out of Las Vegas. He/him.I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too.
Want to advertise here and reach my savvy readers?
