Eager Index Spools
We flag Eager Index Spools for a couple different issues.
- If they’re expensive compared to the overall plan
- If they spool more rows than the query returns
The reason we only care about Eager Index Spools right now is that they cache the entire result set in tempdb, and index it. This makes them generally more prone to spill to disk, and means they can be blocking operations while the data is spooled and indexed.
The other kind of lousy thing about them is that there’s no missing index request generated, even though the optimizer thinks it’ll be cheaper to MAKE ITS OWN INDEX and then run your query. Very passive aggressive.
If they’re spooling more rows than the query is returning, there could be a bad estimate in the plan.
For more information about Spools, check out these links:
Paul White: 3-part series on the Halloween problem
A most excellent dba.stackexchange.com question, in which Joe Obbish gets prescribed some Spool Softener