Types of Spools in Execution Plan – Deep Dive

Today let’s talk about types of spools in detail. The Spool operator saves an intermediate query result to the tempdb database. 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.  I have explained spool operator in details here (https://msbiskills.com/2015/07/26/spools-in-execution-plan-are-they-bad/). Today I am going to talk about the types of spools. So the types of spools are-

1. Table Spool
2. Index Spool
3. Row Count Spool
4. Window Spool
5. Eager Spool
6. Lazy Spool

Let’s first understand each of the above spool in details and then we will proceed for examples-

Table Spool

1. Table Spool is a physical operator.

2. The Table Spool operator scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database. In tempdb it is stored as a Worktable.

3. It exists only for the lifetime of the query. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.

Index Spool

1. Index Spool is a physical operator

2. The Index Spool operator scans its input rows, placing a copy of each row in a hidden spool file (stored in the tempdb database as a worktable and existing only for the lifetime of the query)

3. After this optimizer creates a Nonclustered index on the rows. This will give the optimizer to use the seeking capability of indexes to output only those rows that satisfy the SEEK:() predicate.

4. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.

Row Count Spool

1. Row Count Spool is a physical Operator

2. The Row Count Spool operator scans the input, counting how many rows are present and returning the same number of rows without any data in them.

3. This operator is used when it is important to check for the existence of rows, rather than the data contained in the rows.

4. For example, if a Nested Loops operator performs a left semi join operation and the join predicate applies to inner input, a row count spool may be placed at the top of the inner input of the Nested Loops operator. Then the Nested Loops operator can determine how many rows are output by the row count spool (because the actual data from the inner side is not needed) to determine whether to return the outer row.

Window Spool

1. Window spool is a logical and physical operator.

2. The Window Spool operator expands each row into the set of rows that represents the window associated with it.

3. Example – In a query, the OVER clause defines the window within a query result set and a window function then computes a value for each row in the window.

4. When using the default window frame (Range Unbounded preceding) window spool operator uses an on disk worktable to process the window, but it doesn’t not spill the data to tempdb.

Eager Spool

1. Eager Spool is a logical operator.

2. The Eager Spool operator takes the entire input, storing each row in a hidden temporary object stored in the tempdb database. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.

3. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.

4. The Eager Spool operator builds its spool file in an “eager” manner: when the spool’s parent operator asks for the first row, the spool operator consumes all rows from its input operator and stores them in the spool.

Lazy Spool

1. Lazy Spool is a logical operator.

2. The Lazy Spool logical operator stores each row from its input in a hidden temporary object stored in the tempdb database.

3. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.

4. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.

5. The Lazy Spool operator builds its spool file in a “lazy” manner, that is, each time the spool’s parent operator asks for a row, the spool operator gets a row from its input operator and stores it in the spool, rather than consuming all rows at once.

Some common examples where we find these kind of spools

1. Let’s start with Table and Index Spool. We have a simple recursive CTE query of a very common business problem i.e. Manager and the employee. Check out the query below-

--

;WITH CTE(EmpName , EmpId, Level,FullyQualifiedName) AS (
     Select E.EmpName, E.EmpID, 0 Level
     , Cast('.'+E.EmpName+'.' as Varchar(MAX)) FullyQualifiedName         
     From Employees E Where E.ReportsTo IS NULL
     UNION ALL
     Select E.EmpName, E.EmpID, c.Level + 1 , c.FullyQualifiedName+'.'+E.EmpName+'.' FullyQualifiedName  
     From Employees E INNER JOIN CTE c on c.EmpID = e.ReportsTo 
)
SELECT SPACE(LEVEL*4) + H.EmpName Hierarchy,SPACE(LEVEL*4) + CAST(H.EmpID AS VARCHAR(MAX)) Hierarchy , * FROM CTE H 
ORDER BY H.FullyQualifiedName

--

Let’s go through the execution plan. Here we have 2 spool operators – Table and index spool

Pawan Khowal – Table & Index Spool

2. Window Spool

--

SELECT * , SUM(ID) OVER (PARTITION BY ID ORDER BY FromPoint) rnk FROM dbo.Area

--

Let’s go through the execution plan. Here we have 1 spool operator – Window Spool

Pawan Khowal – Window Spool

3. Row Count Spool

--

SELECT ID, Vendor
  FROM Products 
 WHERE NOT EXISTS(SELECT 1
                    FROM Products 
                   WHERE Dates = '20050101'
                     AND Quantiy > 10)
OPTION(MAXDOP 1)
--

Let’s go through the execution plan. Here we have 1 spool operator – Row Count Spool

Pawan Khowal – Row Count

Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com