Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
4  
Is your UPDATE statement updating an index key used in the WHERE clause or is it performing a self join? If so the eager spool is there to enforce correctness (for halloween protection) and not much you can do about it except change the statement or the index. –  Martin Smith Feb 16 '12 at 15:42
    
@MartinSmith Spot on, old chap. I'm still wondering if anything else comes to mind, but the Halloween issue makes perfect sense. Mayhap I can create a table variable, store the ids of the rows in the table that need to be updated, then use that to join back to the original table, avoiding the UPDATE...WHERE issue. –  Nick Vaccaro Feb 16 '12 at 16:10
4  
Careful with that. What happens when the rows you want to update change between when you load the table variable and then go to update the table? Just avoiding a table spool is not a compelling reason to complicate your logic like this. –  Nick Chammas Feb 16 '12 at 16:30
3  
Also using table variables is a bad idea for stuff like this, since they are immune from rollbacks and commits! –  JNK Feb 16 '12 at 16:46
    
Can you show the plan? Spools can mean many different things. –  usr Mar 30 '12 at 21:19

2 Answers 2

up vote 8 down vote accepted

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 to the avoid 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 SQL Sentry 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.

share|improve this answer
1  
I once had a Halloween attack my server. It was gruesome! Definitely important to have Halloween protection, I can say from personal experience! –  ErikE May 28 '13 at 21:33

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.