Hugo Kornelis (4/22/2010)
Thanks, vk-kirov. I was not aware of this operator.
From the limited information I could find on it, it will be used if the same data is used at least twice in a query, the second needs only a subset of the rows needed for the first (otherwise a normal spool would be used), and the subset is based on a predicate that is sargable and selective enough to earn back the cost of creating the index.
Do you think you can post a repro script that actually uses this operator? Have you ever seen it used in your code?
You can generate an Index Spool easily with an unindexed Tally table:
FROM dbo.Numbers (50000);
FROM #Tally T1
JOIN #Tally T2
ON T2.N < T1.N;
That uses an in-line TVF I keep handy to generate a temporary tally heap - you can use any method that generates a similar object.
Index spools are not all that rare, but the estimated cost of spooling the rows to a worktable, creating the index, and dropping it afterward is quite high, so the optimiser will often choose a different approach, purely on cost grounds (usually a hash join - the example above avoids that by joining on an inequality only).
There are two types of index spool - eager and lazy. The above example generates an eager spool - one that consumes all rows from its input before it can be used to seek with.
A lazy index spool is a bit more like a regular table spool - except it never truncates its worktable (a normal spool only ever caches one result - it truncates its worktable on every rebind).
A lazy index spool accumulates results on every rebind (when the correlated parameter changes). Personally, I'd love a way to be able to hint the use of lazy index spools, but that's another story.
Interestingly, you cannot trust the reported numbers for rebinds and rewinds for an index spool - the internals report a rewind to anything other than the last value seen as a rebind rather than the rewind it actually is. Sad, but true.