Implicit vs Explicit Spools In SQL Server Query Plans

Posted on by Erik Darling

Spools Of A Feather

There are plenty of spools in query plans, and they’re all pretty well labeled.

  • Index
  • Table
  • Rowcount
  • Window

They can be either eager or lazy.

An eager spool will take all the rows at once and cache them, and a lazy spool will only go get rows as needed.

But what else can act like a spool?

Phases On

In general, a blocking operator, or as my upside down friend Paul calls them, “phase separators” can act as a spool.

A spool, after all, is just something that keeps track of some rows, which is exactly what a Sort or a Hash do.

They keep track of rows that arrive, and either sort them according to a need, or create a hash table of the value.

While either of these happen, any downstream work in the query have to wait for them to complete. This is why they’re called blocking, stop and go, or, more eloquently, phase separators.

Eager spools have the same basic feature: wait for all the rows from downstream to arrive, and perform an action (or just feed them to another operator).

Here’s an example of a Sort acting as a spool:

DROP TABLE IF EXISTS #waypops;

CREATE TABLE #waypops
(
  UserId INT
  , PRIMARY KEY NONCLUSTERED (UserId) WITH (IGNORE_DUP_KEY = ON)
);

INSERT #waypops WITH(TABLOCKX) 
       ( UserId)
SELECT b.UserId
FROM dbo.Badges AS b
WHERE b.Name = N'Popular Question';
A SQL Server query plan
Enjoy the silence

The Sort is in the same order as the index it’s reading from, but just reading from the index wouldn’t provide any separation.

Just Passing By

This is weird, niche stuff. That’s why I’m posting it on a Friday. That, and I wanna bully someone into writing about using a hash join to do the same thing.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts