The index spool execution plan operator optimizes access to an intermediate result set by building a temporary nonclustered index in tempdb.
The Index Spool operator scans its input rows, placing a copy of each row in a hidden spool file (stored in the tempdb database and existing only for the lifetime of the query), and builds a nonclustered index on the data. This allows use of the seeking capability of indexes to output only those rows that satisfy a suitable predicate in a query plan.
An Index Spool is often an indication that optimal permanent indexes are not present in the database. The temporary index is only available to the query for which it is built, and will be fully rebuilt for every execution.
Index Spool can operate in Lazy or Eager mode. In lazy mode, rows are added to the index on demand. In eager mode, the operator consumes its entire input and builds the temporary index when the operator initializes. Unlike Table Spools, which only keep the most recently bound results for replay (rewind), Index Spool can rebind to previously-encountered rows as well as rewind.
The Index Spool's Seek properties define the column(s) on which the temporary nonclustered index is built. The Output List of the operator shows the key columns and any included columns. The output list may also be empty, in which case the spool is being used to check for the existence of a match only.
More details on the Index Spool can be found in this Simple Talk article.
