Spools In SQL Server

Best and most simple explanation I could find on web for Spool is - "Spool is a physical operator that shows up in the execution plan. 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" - from this Q&A.

Query optimizer reads and calculate computed data and put it on a worktable in tempDb when it thinks reading data from original table is too much cost (cost based optimization).

There are several types of spool operators in a execution plan:

* Table Spools -> Physical Operator

* Index Spools -> Physical Operator

* Row Count Spools -> Physical Operator

* Window Spools -> Logical/Physical Operator

* Eager Spools -> Logical Operator

* Lazy Spools -> Logical Operator

Table Spool - Read a table and create a work table that only exists for the life time of the query. 

Index Spool - Reads a table(s) and create a work table and also create a non-clustered index on it. Exists only for the life time of the query.

Row Count Spool - Reads a table and returns count or rows, used when need to check existence of rows rather than data.

Window Spool - Expand each row into set of window associate with it (window functions)

Eager Spool - Scans entire input (table) and stores, even when ask for first row (eager operation)

Lazy Spool - Build the work table in lazy manner, i.e. row is stored when parent operator ask for it.

I used following articles to extract above for my knowledge and recommend you to read in deep if you like more details:

* https://msbiskills.com/2015/09/02/types-of-spools-in-execution-plan-deep-dive/ -> by Pawan Khowal

* https://learn.microsoft.com/en-us/answers/questions/463552/execution-plan-for-table-spool