Spools in Execution Plan. Are they bad?

So what is a Spool?

1. Spool is a physical operator that shows up in the execution plan.
2. Spool operator effectively saved an intermediate result to the tempdb for further usage.
3. Consider Spools are a cache in the query processor.

How they are implemented?

Spools are implemented as hidden tables in tempdb. Means you get all of your data in tempdb and then queries it. Query optimizer uses spools when it thinks that it’s better to put data in the temp table rather than hitting a table again and again using seeks and scans. So what it does is, it picks data from the table and saves that in the temp table as a hidden table and uses it whenever query optimizer requires it.

Note – Please note that this hidden table is not same as temp table. Consider this as a hidden table (Work table) for a particular query.

Why we have spools in execution plan?

• This is because you didn’t create proper indexes perhaps. Example Unique key, Primary Key, etc.
• You didn’t provide correct information to the query processor about the uniqueness of your data.
• Sometimes even you have unique key or primary key on your table, optimizer losses it along the way due to the behaviour of various iterators in the execution plan. We can bring this case by using keyword distinct to tell the optimizer that the data is unique and it doesn’t have to cache.
• Recursion while updating data( The Halloween problem )

Are spools bad?

1. Well Optimizer uses spools as a performance optimization technique. So effectively we cannot say it a bad or good.

2. Consider a scenario where you have pick all data from a physical table and put that into tempdb and then optimizer queries it, and again pick another table put that in the tempdb and then queries, Now will that a fast query? probably not. So spools are not good in most of the cases. I will explain scenarios like how we can identify bad things like spools and how to avoid them in the upcoming posts.

Types of Spools

In the upcoming posts, I will explain different types of spools and how we can avoid then(if possible).

I hope you have enjoyed the article. Thanks for reading !

-Pawan Khowal

MSBISkills.com