Question: What is TempDB Spill in SQL Server?
Answer: When SQL Server poorly (incorrectly) estimates the number of rows that will be returned from any operator, it requests an incorrect amount of memory grant from the SQL Server engine, which leads to an inefficient execution plan. If the inefficient plan has a relatively small memory grant, SQL Server will need additional space on the disk to do the necessary work (like join, order by). When SQL Server uses TempDB when any query does not have enough memory to do its operation, it is called TempDB Spill.
Whenever you see any execution plan with a TempDB Spill, trust me there are always chances that you can improve that queries performance. Here are a few generic suggestions:
- Update statistics with full scan
- Create covering indexes
- Use temp tables instead of subqueries
- Divide a huge query into multiple small queries
- Re-write queries to avoid parameter sniffing
- Many more…
There are many different ways you can improve the performance of the query, and not anyone solution works for every query.
Whenever I see the TempDB Spill my personal instinct is to look at the query and try to understand if there is anything we can do to avoid the spills without changing anything in the schema.
In recent times, I have seen parameter sniffing also big trouble during my Comprehensive Database Performance Health Check. Here is a set of articles that I have written that can be very helpful if you are facing any performance problem due to parameter sniffing issues.
- SQL SERVER – Parameter Sniffing Simplest Example
In this blog post, we discuss what actually is parameter sniffing and how we can overcome it with the help of recompiling the stored procedure. - SQL SERVER – Parameter Sniffing and Local Variable in SP
It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure. - SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. A very underutilized technique indeed. - SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing. - SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance. - Performance and Recompiling Query – Summary
This post summarizes the entire series of parameter sniffing, performance and recompiling query.
Reference: Pinal Dave (https://blog.sqlauthority.com)
