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.