sp_BlitzCache™ Result: TempDB Spills

Queries are spilling out to tempdb. This means that SQL Server has poorly estimated the amount of rows that will be returned from an operator. When the row estimate is wrong, the memory grant will be wrong; SQL Server is going to have to use extra space on disk to do the work.

How to Fix the Problem

Remus Rusanu’s Understanding Hash, Sort and Exchange Spill events provides a high level overview to the different kinds of spill you may encounter as well as links to Microsoft documentation about how to solve the problem.

The best course of action is examining queries and statistics. Identify why SQL Server is generating inaccurate estimates of rows returned – this caused by functions in predicates, bad statistics, table valued functions, or missing indexes.

Solving the problem depends on the underlying problem – it can be as simple as adding a new index or as complex as rewriting a query.

To find the queries that are causing spills, you have several options – you can capture and examine actual execution plans, or you can collect exchange spill events through extended events. Paul White covers your options in his reply to What frequency of hash/sort spills into tempdb is concerning?