Hugo Kornelis takes a look at index spools:
The Index Spool operator is one of the four spool operators that SQL Server supports. It retains a copy of all data it reads in an indexed worktable (in tempdb), and can then later return subsets of these rows without having to call its child operators to produce them again.
The Index Spool operator is quite similar to Table Spool, except that Index Spool indexes its data, giving it the option to return a subset, and Index Spool lacks the option to read data from a spool created by another operator. The other two spool operators are quite different: Row Count Spool is optimized for specific cases where the rows to be returned are empty, and Window Spool is used to support the ROWS and RANGE specifications of windowing functions.
Eager and Lazy Spool operators rank high on my list of “troublesome when I see them” operators. The reason is not so much that eager or lazy spools are inherently bad—they’re not, as they are efficient ways to perform a particular query given the constraints of that query—but if I see one of them in conjunction with a slowly-performing query, it’s a good sign that I want to optimize away the need for spooling.