Contents
What causes table spool in SQL Server execution plan?
I have a query that creates several temporary tables and then inserts data into them. From what I understand this is a potential cause of Table Spool. When I look at my execution plan the bulk of my processing is spent on Table Spool. Are there any good techniques for improving these types of performance problems?
Which is the most basic of the table spool operators?
Table Spool is probably the most basic of the spool operators. The Index Spool operator is very similar to it, but indexes its data to allow it to return only a subset of the stored rows. The Row Count Spool operator is optimized for specific cases where the rows to be returned are empty.
How does a table spool work in tempdb?
It retains a copy of all data it reads in a worktable (in tempdb) and can then later return extra copies of these rows without having to call its child operators to produce them again. These copies can be made available in the same part of the execution plans, or in another part.
Why is table spool always displayed with lazy spool?
A Table Spool that processes segmented input is always displayed with “Lazy Spool” as the logical operation. The actual behavior can be described as halfway in between eager and lazy, because it does not eagerly store all input but also does not pass each row immediately as it is being read.
When to create a work table in tempdb?
When the query plan for a common table expression query uses a spool operator to save intermediate query results, the Database Engine creates a work table in tempdb to support this operation.
Why is table spool preffered by engine Whithout?
But is really hard to guess why the spooling is preffered by the engine whithout knowing any specifics. I recommend you go over Craig Freedman’s blog, he is an engineer in the query optimizer team and has explained a lot of the inner workings of SQL 2005/2008 optimizer.
How to get rid of eager spool in SQL?
So in this case, the Eager Spool forces the read of the @oriopr table to be completed before any new rows are inserted into the @oriopr table. You can get rid of the Eager Spool step by rewriting the function so the query so that it does not both read from and update the same table in the same query. For example you could do