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.
Best Answer
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.