Optimizing SQL Server: Understanding the much-feared spool operators

In the world of SQL Server, the Spool Operator is often regarded with a mix of fear and confusion by many. It’s one of those elements that can significantly impact query performance, but understanding how and when it gets used can be a game-changer for optimizing execution plans. In this post, we’ll dive into the Spool Operator, explaining what it does, why it’s used, and how you can recognize it in your execution plans. Let’s demystify this often-overlooked operator and see how we can harness its power to write more efficient queries!

What is the Spool Operator?

A Spool operator in SQL Server utilizes a temporary worktable to store data that may be reused multiple times within an execution plan. By saving intermediate results in a worktable, the optimizer can reference this data multiple times without the need to re-execute the same set of operators. This can improve efficiency by reducing redundant computations.

While the Spool Operator is an optimization technique, it is sometimes considered undesirable because it can introduce additional I/O overhead and slow down the query if not used properly.

Spool operators come in several types, each represented by a physical operator: Index Spool, Rowcount Spool, Table Spool, and Window Spool. In SQL Server, spools are always backed by a clustered index, and an Index Spool has an additional non-clustered index to facilitate faster data retrieval.

Now, let’s focus on Index Spool and Table Spool! New content will be published soon!

There are also two logical types of Spool operators: Lazy Spool and Eager Spool. A Lazy Spool is a streaming operator; it fetches a row from its child operator, stores it in the worktable, and then passes it to its parent operator, relinquishing control to the parent. In contrast, an Eager Spool is a blocking operator. It retrieves all rows from its child node before returning any data to its parent, which can lead to performance issues.

Although the optimizer generally seeks to avoid the use of Eager Spools, they are useful in specific scenarios, such as preventing issues like Halloween protection.

Why Does SQL Server Use the Spool Operator?

SQL Server uses the spool operator in scenarios typically when it deems it more efficient to store a result set temporarily rather than recalculating it multiple times.

Examples where you might see the Spool Operator in an execution plan:

1. Subqueries and Derived Tables: A subquery or derived table is used in a query and the same data is needed multiple times, SQL Server may decide to store the intermediate result in a spool. This prevents the subquery from being executed repeatedly, which can be a performance bottleneck.
2. Self-Joins: If you have a self-join where the same table is joined with itself, SQL Server may spool the result set of one side of the join and reuse it to match rows from the other side of the join.
3. Complex Queries with Multiple Filters: Queries with complex WHERE conditions, GROUP BY, or HAVING clauses may result in SQL Server spooling intermediate data to optimize access to that data in subsequent operations.
4. Reducing Repeated Computation: If SQL Server determines that a particular part of the query (e.g., aggregation or sorting operation) is computationally expensive and needs to be reused, it may spool the result to avoid redoing that calculation for each row.

Performance Considerations of the Spool Operator

While the Spool Operator can be a helpful optimization tool, it can also lead to performance issues if not used appropriately, let’s check a few factors to consider:

• I/O Overhead: If the spooled data is large and stored in tempdb (which can be slower than memory), it may lead to additional disk I/O, which could slow down the query performance.
• Memory Usage: When SQL Server uses the Table Spool in memory, it can quickly consume a large amount of memory, particularly if the spooled data is substantial. This may impact the overall performance of the server and affect other queries running concurrently.
• Query Plan Cost: A Spool Operator introduces an additional step in the query execution plan, which can increase the overall cost of the plan. If SQL Server is not careful in choosing when to use a spool, it could end up causing unnecessary overhead.

How to Optimize Queries with Spool Operators

If you notice that SQL Server is using a Spool Operator and it is causing performance issues, here are some optimization strategies to consider:

1. Rewrite Subqueries: Sometimes, rewriting subqueries as joins or common table expressions (CTEs) can help SQL Server avoid using a spool.
2. Improve Indexing: Ensure that appropriate indexes are in place for the columns involved in the query, as this can reduce the need for spooling and improve join performance.
3. Limit the Result Set: Try to reduce the amount of data being spooled by adding filters or optimizing the query to work on smaller result sets.
4. Check Query Plan for Alternatives: Look for alternative execution plans. Sometimes SQL Server chooses a spool when other join strategies (e.g., Nested Loops or Hash Join) might be more appropriate.

Let’s do it ourselves

You can get more details about this reading the book: SQL Server Execution Plans by Grant Fritchey — Third Edition.

All code from here to end you can reproduce using an AdventureWorks2019 Databases.

Example 1: Table Spool

USE AdventureWorks2019
GO
SELECT sp.BusinessEntityID, 
  sp.TerritoryID,
  ( SELECT SUM(s.TaxAmt) 
   FROM Sales.SalesOrderHeader AS s 
   WHERE s.TerritoryID = sp.TerritoryID) 
FROM Sales.SalesPerson AS sp
WHERE sp.TerritoryID IS NOT NULL
ORDER BY sp.TerritoryID;

The first part of the Nested Loops join operator (the outer input) scans the clustered index on the SalesPerson table, returning 14 rows, sorted by TerritoryID.

This means the inner part of the operation, which is a Table Spool, will run 14 times.

The first time the inner part runs, it performs a Rebind (we’ll talk more about Rebind and Rewind later).

During this step, the Table Spool gets a row from the Hash Match operator, which then fetches a row from the Clustered Index Scan on the SalesOrderHeader table. The Hash Match uses a temporary hash table to calculate the total tax amount for each unique TerritoryID in SalesOrderHeader. Since there are 10 different TerritoryID values, the Hash Match will eventually return 10 rows to the Table Spool, which stores them in its worktable and passes them on (this is called a Lazy Spool — a good thing). It keeps sending these 10 rows until all are passed on.

When we look at the Nested Loops operator, we see it uses a Predicate to satisfy the join condition (we can check this here).

In simple terms, the inner part of the operation is static, meaning it will return the same results for each value in the outer part.

For the other 13 rows returned from SalesPerson to the Nested Loops operator, the outer part has to rewind.

This is where the Table Spool is helpful!!! Instead of calling the Hash Match again 13 times, it uses the stored data from the Table Spool’s worktable.

If you look closely at the properties of the Table Spool, you’ll see it has 13 Rewinds and 1 Rebind.

The Hash Match and Clustered Index Scan are only executed once for the initial Rebind to load the data into the Table Spool.

This is a simple example of how the SQL Server optimizer uses the Table Spool to make aggregation queries more efficient by reusing previously stored data instead of recalculating it.

Example 2: Index Spool

To see an Index Spool operator, we just need to add a useful index that the optimizer can use to find rows with matching TerritoryID values in the SalesOrderHeader table.

USE AdventureWorks2019
GO
CREATE INDEX IDX_TERRITORYID 
ON Sales.SalesOrderHeader
( TerritoryID )
INCLUDE (TaxAmt);

Now, re-run the query and check the execution plan:

We now see an Index Seek on the SalesOrderHeader table and a streaming aggregation instead of the blocking Hash Match aggregation. Also, the Index Spool is used instead of the Table Spool.

As we know, the 14 rows returned by the SalesPerson table scan are sorted by the TerritoryID in a Sort operation. Let’s take a look at the properties of the Nested Loops operator:

Wow! We can see that the join condition is satisfied using TerritoryID values as Outer References (no Predicate as we saw before). This means that each of the 14 rows’ values is passed down to the inner part, which then returns only matching rows based on the Index Seek operation.

As before, the first time the inner input runs is a Rebind. The first TerritoryID value (1) is passed down to the inner operators. The Index Spool initializes its child operators. The Stream Aggregate starts fetching rows from the Index Seek, using the pushed-down value to find matching rows in the index. The spool sends these matching rows to the Stream Aggregate, which returns a single result (the aggregated TaxAmt) to the Index Spool, which stores it in an indexed worktable and sends it back to Nested Loops.

For the second and third rows in the Nested Loops, the TerritoryID value is still 1, so these executions of the Index Spool are Rewinds. The Index Spool skips the Stream Aggregate and immediately returns the previously stored results from the worktable.

For the fourth row, we encounter a new TerritoryID value (2). This causes the Index Spool to perform a Rebind, reinitializing the operators with the new pushed-down value. This is the fourth execution of the Index Spool, but only the second for each child operator. This pattern repeats until all 14 rows are processed.

Let’s take a look at the properties of the Index Spool: We can see there are 10 Rebinds and 4 Rewinds.

Also, by examining the properties of the Stream Aggregate and Index Seek, we see only 10 executions, corresponding to the 10 unique TerritoryID values in the 14 rows.

Conclusion

In this post, we learned about the Spool Operator in SQL Server, which is often misunderstood. Spools use temporary worktables to store and reuse data, so SQL Server doesn’t need to repeat the same calculations. This can improve performance, but if not used correctly, it might cause extra I/O or memory problems. We also looked at examples of Table Spool and Index Spool in action, showing how they can boost query performance, especially in complex calculations. By understanding how and when to use spools, you can make your queries run more efficiently.