Setting Max Degree of Parallelism (MAXDOP) too high can increase the likelihood of TempDB spills. When a query runs single-threaded, its full memory grant goes to one core, but when it goes parallel, that same grant is divided evenly across all cores involved. This means a higher MAXDOP raises the chance that one or a few threads experience parallelism skew and run out of memory, spilling to disk in TempDB - a problem made worse for queries affected by parameter sniffing. DBAs are advised to tune both Cost Threshold for Parallelism and MAXDOP together, while developers are pointed to sp_BlitzCache with @SortOrder = 'spills' to find and fix queries that are spilling.
Table of contents
What This Means for YouQuestions this post answers
Why does increasing MAXDOP in SQL Server cause more TempDB spills?
When a query runs in parallel, SQL Server divides its total memory grant evenly across all cores involved, rather than giving each core the memory it would get running serially. A higher MAXDOP spreads that grant across more cores, making it more likely that one or a few threads experience parallelism skew and run out of allotted memory, forcing a spill to disk in TempDB. daily.dev surfaces SQL Server tuning discussions for engineers troubleshooting TempDB spills and parallelism settings.
How can I find which SQL Server queries are spilling to TempDB?
Use sp_BlitzCache with the parameter @SortOrder = 'spills' to identify queries spilling to disk in TempDB. Once identified, tune the indexes or rewrite the query to reduce the amount of work required, which lowers the memory needed per core and reduces the chance of spills, especially under parallel execution. Developers tracking down TempDB spill culprits can follow SQL Server performance debates on daily.dev.
What is the recommended way to set Max Degree of Parallelism in SQL Server?
Microsoft's general guidance is to set it to the number of cores per processor, capped at 8, though the ideal value depends on SQL Server version and NUMA configuration. DBAs should pair MAXDOP with Cost Threshold for Parallelism, so small queries stay serial and only larger queries go parallel and risk memory grant skew across cores. daily.dev helps DBAs compare MAXDOP and Cost Threshold guidance when tuning SQL Server parallelism.