To understand SQL Server Table Spool Operator, let us consider following query as an example.
FROM Sales.SalesOrderDetail SD1
WHERE SD1.OrderQty > (SELECT AVG(SD2.OrderQty) FROM Sales.SalesOrderDetail SD2
WHERE SD2.SalesOrderID = SD1.SalesOrderID)
For each SalesOrderID from the SalesOrderDetails table the sub-query returns the average value of all OrderQuantity and after that average is compared with principal query to filter each SalesOrderID’s with values higher than their average.
Lazy Spool reads data only when individual rows are required and creates a temporary table then build this table in a “lazy” manner; that is, it reads and stores the rows in a temporary table only when the parent operator actually asks for a row.
As can be seen, Spool operator displayed three times in execution plan but that doesn’t mean that three temporary tables are being created in memory that to say, all the spools are using same temporary table and this can be verified if we look at the operators hints displayed in execution plan.
As we see, the first spool hint has NodeID 2 and other two has referenced Primary Node ID 2. Next two days we are going to discuss on the execution plan for the query used in our example.