Database Animations: Why Higher Maxdop Equals More TempDB Spills
You’ve probably heard of the setting Max Degree of Parallelism. I hate that name: it should really be called just plain ol’ Degrees of Parallelism, and here’s why.
There are a lot of conflicting opinions out there about how to set it, and Microsoft has official guidance about it in that article above. It’s basically set it to the number of cores per processor, up to 8, but no higher than 8. (It changes a lot depending on SQL Server version and NUMA config, but if I had to summarize it in one sentence, woop, there it is.)
So, what’s the harm in going higher?
To understand why, we first gotta understand that when queries run out of memory, SQL Server won’t usually grant more memory on the fly. For example, if your query starts dealing with a much bigger amount of data to sort than it’d initially expected, it simply dumps that extra data to disk in TempDB:
Thing is, that animation assumes that your query is going single-threaded. When your serial query gets a memory grant, all of that memory is granted to that one single core. (If you have multiple sorts in the same plan, or at least multiple operations that need memory, the memory’s divided up between the operators – but that’s outside of the scope of this post.)
When your query goes parallel, the grant is divided evenly across all of the cores!
That means the higher you set maxdop, the more likely it is that one individual thread (or a few) is going to experience parallelism skew, and end up spilling to disk.
This is especially rough for queries that deal with parameter sniffing: for small-data parameters, all of the work can get assigned to just one core simply because there aren’t that many rows. However, at high maxdop settings, the tiny percentage of memory that one core gets can mean memory spills for that one core.
What This Means for You
If you’re a DBA, that means you wanna set both Cost Threshold for Parallelism (to make sure small queries don’t go parallel) and Max Degrees of Parallelism (so queries don’t go too wild and crazy when they do go parallel.)
If you’re a developer, that means you want to use sp_BlitzCache @SortOrder = ‘spills’ to track down which queries are spilling to disk, and tune the indexes or the query to reduce the amount of work required, thereby also reducing the likelihood that they’ll need more memory than they can get on any given core.
If you liked this, check out the other posts in my Database Animations series.
Update with Demos: Mo Cores, Mo Spills
I got a couple of questions privately saying, wait, that can’t be right – that has to be an AI hallucination. No, check out this demo query with one of the large versions of the Stack Overflow database. All of the actual query plans show parallel skew, with the majority of work being done by just one CPU core. That core gets progressively less memory as our MAXDOP scales up, even though SQL Server is adding more memory to the plan overall. As that core gets less memory, it spills progressively more to disk on this 64-core server:
- MAXDOP 2: spills 58,025 pages
- MAXDOP 4: 82,517
- MAXDOP 8: 92,418
- MAXDOP 16: 114,873
- MAXDOP 32: 136,176
- MAXDOP 64: 145,385
On the 64-core plan, the overall memory grant situation is dire:
The query was granted 132,096 KB of memory, and only used 31,704 KB – leaving 100MB unused – but that one poor core doing all the work has completely exhausted his part, and he’s forced to write over 145K pages to disk when there’s lots of memory available left to the query’s grant overall.
The answer isn’t necessarily to lower MAXDOP – although 64 is usually a pretty bad setting. (Amusingly, one of the LinkedIn commenters actually suggested >128 can be good for queries that need to scan large amounts of memory, and boy, do I have questions about that environment.) The better answer is usually to tune indexes and queries to avoid the amount of work being done in the first place, which reduces the parallelism & spill problems too.
Note: I generated the animations in this post with Claude Code, but all of the text & demos are completely written by me.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
I think the robots got away from you on this one.
Whoa – what do you mean?
So you have a reporting SQL Server Enterprise Edition and there are literally hundreds of thousands of queries running all day with all kinds of cross db joins, etc. Is there a general set it (and monitor it) that can/should safely be applied across the dbs or server based on #cores? Great article just was left with exactly what next step to take. TY!
That’s a great question but it’s outside of the scope of this post, and more than I can answer quickly in a blog post comment. Sorry about that!
Is it just me or does this sound like a problem with how parallelism handles these types of scenarios? It seems inefficient and like this issue could be resolved by MS.
By MS do you mean this is a Microsoft issue that might be handled with a proper default setting based on cores? I think I understand there isn’t necessarily a one size fits all solution but it’s also impractical to adjust these settings for a 100~ databases on an instance of a reporting server, or do make a default setting across dbs server level and constantly tweaking it as activity ebbs and flows. I might agree this is simply a short-coming of the server itself, considering the animation and the splitting that occurs with a multi-core Max Dop setting.
I am also not sure how to set the other setting. Thanks for any guidance!
Its just you. Sequential events cannot be parallelized ever and a parallel plan is a plan of a single unit of work divided up into multiple units of sequential work. That work does not divide up exactly evenly and the more it gets divided up, the more opportunity for unbalanced parallelism there are. Even on Oracle, which implements some assembly code (at extreme costs of development and testing) to modify hardware allocations on the fly when a planned execution doesn’t go as planned, the upper limit in parallelism is 16 cores, but few workloads will ever benefit from more than 8.
SQL Server now implements some features to adapt to bad parallelism after the fact, but without the development/testing due to memory safety risks of dynamically adding or subtracting hardware on the fly the way oracle can.
Even in cloud data warehouses where the data is totally static, every vendor that implements mpp does it by vertically sharding tables at known boundaries that comes at the cost of much hihger compilation time.
By MS do you mean this is a Microsoft issue that might be handled with a proper default setting based on cores? I think I understand there isn’t necessarily a one size fits all solution but it’s also impractical to adjust these settings for a 100~ databases on an instance of a reporting server, or do make a default setting across dbs server level and constantly tweaking it as activity ebbs and flows. I might agree this is simply a short-coming of the server itself, considering the animation and the splitting that occurs with a multi-core Max Dop setting.
I am also not sure how to set the other setting. Thanks for any guidance!