Bert Wagner takes us through some of the more common execution plan operators:
Spools come in a variety of types, but most of them can be summarized as operators that store an intermediary result table in tempdb.
SQL Server often uses spools to process complex queries, transforming the data into a tempdb worktable to allow further data operations. The downside to this however is the need to write the data to disk in tempdb.
When I see a spool, I first often try to think if there is a way to rewrite the query to avoid the spool in the first place.
Spools are my bete noire. I love what Erik Darling calls them: SQL Server passive-aggressively telling you that you need an index without actually saying that you need an index.
At the end of the post, Bert calls out Hugo Kornelis’s operator list. Definitely check that out too, as Hugo is putting together a masterpiece in the original meaning of the term.