There are a few operators in SQL which use the memory to store the data for operations like sorting and join.
These operators request for memory grant to keep the data in memory, but in some cases when a server is under memory pressure and it doesn’t get the requested memory, these operators use the temp DB to store the data. This is known as a temp DB spill.
SORT, HASH Joins and Hash Match are some operators that use the memory to perform sorting, join or aggregate.
SORT uses the memory to keep the data to perform the sort operation while HASH JOIN and Hash Match use the memory to create a Hash table in memory to perform the join,aggregate etc.
If SQL server doesn’t have enough memory to perform these operations in memory, then it will use temp db and you will see a warning sign on these operators.
Below are few execution plans with temp db spill.
You can see the reason of warning If you will take the cursor on these operators.
As operators are forced to use temp db so it will have to use disk. No matter how fast your disk is but it can’t be as fast as
memory and it will slow down your queries.
This warning sign will appear at the run time. You will not find it if you will see the cached plan for your query.
Below is the cached plan for same query.
How to avoid temp db spill
1. Avoid memory pressure situations and keep your server healthy
3.Queries normally choose hash join because of lack of good indexes and it can’t use merge join to avoid sorting. Creating proper index for your query will resolve the issue.
4.Avoid run time sorting by having adequate indexes for your order by clause
5.Update the stats or create new stats to help cardinality estimator to choose better execution plan