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.

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.

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.