Table Spool (Lazy Spool) operator in SQL Server execution plan

The table spool (lazy spool) operator is used to temporary store intermediate rows in the tempdb (like eager spool). However the eager spool store the rows as soon as they are produce, the Lazy spool operator only stores row when they are needed by parent operator.

It generate on the below scenarios

Ø  Recursive CTE

Ø  Correlated sub query

Ø  Cross apply

Ø  SQL Server anticipates that not all rows will be needed at once or some rows might never be needed.

Lazy spool holds rows in the tempdb for reuse in the query.

Icon of Table spool (Lazy spool)

See the example

Recursive CTE

 See the below example. Creating a table and inserting some records as below

CREATE TABLE employees   (      employeeid   INT PRIMARY KEY,      managerid    INT,      employeename NVARCHAR(100),      salary       DECIMAL(10, 2)   );  -- Insert some sample data INSERT INTO employees             (employeeid,              managerid,              employeename,              salary) VALUES      (1,NULL,'Rajesh',100000),             (2,1,'Mahesh',80000),             (3,1,'Ganesh',75000),             (4,2,'Bagesh',60000),             (5,3,'Ravi',65000);

Writing recursive CTE to get the hierarchy of the employee.

WITH EmployeeHierarchy AS (   SELECT     EmployeeID, ManagerID, EmployeeName, Salary   FROM Employees    WHERE  ManagerID IS NULL   UNION ALL   SELECT     e.EmployeeID, e.ManagerID, e.EmployeeName,e.Salary   FROM  Employees e    INNER JOIN EmployeeHierarchy eh    ON e.ManagerID = eh.EmployeeID ) SELECT   * FROM   EmployeeHierarchy;

 See the execution plan

See the another example

Cross apply

See the below query

SELECT TOP 100 soh.salespersonid,                soh.accountnumber,                total FROM   sales.salesorderheader AS soh        CROSS apply (SELECT Sum(subtotal)                     FROM   sales.salesorderheader SH                     WHERE  sh.modifieddate = soh.modifieddate                     GROUP  BY sh.accountnumber) AS tpl(total)

Running this query and see the execution plan.