Why SQL Server Starts Spilling to TempDB. The "45 Seconds DBA Series" – What Real DBAs Check First | Part 2 🥇
👉 If you found this deep-dive helpful, feel free to check out the ads—your support helps me keep creating high-quality SQL Server content for the community.
Why SQL Server Starts Spilling to TempDB
45 Seconds DBA Series – What Real DBAs Check First | Part 2 🥇
In this post, I’ll show you why SQL Server starts spilling to TempDB—and how to detect it in seconds before it destroys your performance.
✔️ Spills happen when memory grants are insufficient 💣
✔️ Internal operations (sort/hash) overflow to TempDB ⚠️
✔️ Execution plans may look fine—but runtime is not 🚨
✔️ Spills are silent performance killers 🧠
Hi SQL SERVER Guys,
As we say in the previous post, when CPU is fine, IO is fine and Queries look “OK” nut performance is unstable: sometimes fast and sometimes slow, the real issue is often: spills to TempDB. In this Part2 we go deeper in the root cause!
🧠 What spill really is
A spill happens when SQL Server does not have enough memory to execute an operation.
Typical operators involved are
- Sort (ORDER BY)
- Hash Join
- Hash Aggregate
- 👉 SQL Server estimates memory needed.
- 👉 Allocates a memory grant.
- 👉 Starts execution.
but if the memory is not enough: It writes intermediate data to TempDB.
This is called a spill.
📚 Official reference:
Memory Grants - Microsoft Docs
🧪 How to detect spills in 45 seconds
SELECT TOP 10 qs.total_spills, qs.execution_count, qs.total_spills / qs.execution_count AS avg_spills, qt.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt WHERE qs.total_spills > 0 ORDER BY avg_spills DESC;
If you see values here: You already have a performance problem.
📚 Official reference:
sys.dm_exec_query_stats - Microsoft Docs
🔍 Why spills happen
- ❌ Bad cardinality estimation
- ❌ Outdated statistics
- ❌ Parameter sniffing
- ❌ Data skew
- ❌ Wrong execution plan
but the end is always the same:
The optimizer thinks it needs little memory but it reality it needs much more and the result is that that TempDB becomes the fallback.
💣 Why spills are dangerous
- ✔️ No explicit error
- ✔️ No blocking
- ✔️ No obvious alert
But
- 💥 Massive slowdown
- 💥 TempDB pressure
- 💥 Unstable performance
This is why spills are called:
🚨 Silent killers.
🧪 Real-time indicator (TempDB usage)
SELECT
session_id,
internal_objects_alloc_page_count * 8 / 1024.0 AS InternalMB
FROM sys.dm_db_session_space_usage
ORDER BY InternalMB DESC;Look to the results if you have:
👉 High InternalMB = high probability of spills.
📚 Official reference:
sys.dm_db_session_space_usage - Microsoft Docs
🚀 My REAL Strategy
DO not “optimize TempDB”.
👉 Go straight to the cause:
- ✔️ Identify spilling queries
- ✔️ Analyze the actual execution plan
- ✔️ Check estimated vs actual rows
- ✔️ Validate memory grant
💣 Key insight:
TempDB is the effect, not the cause.
If you fix the plan → spills disappear.
📢 Conclusion. How to proceed
To be clear:
- Spills are not rare.
- They are everywhere.
...and most people never see them.
This is because they look at CPU, IO and Plans, instead of runtime behavior but, if your queries are unstable: Check spills.
Because when SQL Server runs out of memory… TempDB will tell you first 😉
📢 Support the Blog: Did you find this deep-dive helpful? The ads you see here are selected to reflect your interests. If a partner's offer catches your eye, give it a look! Your engagement helps me continue publishing premium SQL Server content for the community.
Biondi Luca @2026 - Sharing over 25 years of Gained Knowledge for Passion. Share if you like my posts!
Comments
Post a Comment