What Problems Does an Eager Spool Indicate

Question

Currently running on SQL Server 2008 R2

I am attempting to increase performance of an UPDATE statement. I notice an Eager Spool operation in the showplan popping up. My understanding of spooling operations is pretty basic - they create temporary storage for the table during the update.

I also know that, while they are preventing much worse execution times, eager spools are often indicative of underlying problems with table structure and/or query statements.

My question is pretty simple: When you see an Eager Spool in your query plan, what problems do you first look to address?

I will be analyzing every part of our system to increase performance - I'm just looking for guidance as to where I should start.

Answer 1

I am attempting to increase performance of an UPDATE statement. I notice an Eager Spool operation in the showplan popping up.

Eager Spools may be added for a variety of reasons, including for Halloween Protection, or to optimize I/O when maintaining nonclustered indexes.

Without seeing (even a picture of) the execution plan, it is hard to be certain which of these scenarios might apply in your particular case. If data sensitivity is a concern, consider uploading an anonymized version of the plan for analysis using SentryOne Plan Explorer.

It may well be that the Eager Spool is not the thing you should be concentrating on anyway; many factors influence the actual performance of queries that change data. If you're basing your tuning efforts on the estimated percentage cost shown for the Eager Spool operator, please consider that those estimates are generated using a model that is not intended to match the capabilities of your particular hardware configuration.

Answer 2

Sometimes the eager spool can be avoided when other blocking operators are in place - sorts for example. So it is a good idea to ensure that the data is already sorted by the time it reaches that stage. (missing indexes perhaps?)

If it is there for Halloween protection then as the other guys have said there is little you can do.