Index Spool operator in SQL Server execution plan

The index spool operator in SQL server is a special operator used in the execution plan to create a temporary, in-memory index and stored it into tempdb to optimize query performance when accessing data multiple time. This temporary index is built during the query execution and discard when query execution is completed. It create a temporary non-clustered index on fly to optimize repeated data access. It is used in scenarios where a suitable index was not present.

There are two type of index spool available on execution plan

Eager Index Spool:  The temporary index is created as soon as the query execution hit spool operator.

Lazy Index Spool: The temporary index is created only when necessary index the data as it is needed by parent operator.

Icon of the Index Spool operator

 

See the example

Creating a table and inserting some records

CREATE TABLE  Employees (     EmployeeID INT PRIMARY KEY,     ManagerID INT,     EmployeeName VARCHAR(100) );  INSERT INTO Employees (EmployeeID, ManagerID, EmployeeName) VALUES (1, NULL, 'Bagesh'),        (2, 1, 'Rajesh'),        (3, 1, 'Mahesh'),        (4, 2, 'Ganesh'),        (5, 2, 'Suresh');

Table created and records are inserted.

Now run the below query.

WITH employeehierarchy      AS (SELECT employeeid,                 managerid,                 employeename          FROM   employees          WHERE  managerid = 1          UNION ALL          SELECT e.employeeid,                 e.managerid,                 e.employeename          FROM   employees e                 INNER JOIN employeehierarchy eh                         ON e.managerid = eh.employeeid) SELECT * FROM   employeehierarchy

See the result.

See the execution plan