Tell me more ×
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.
up vote 3 down vote favorite

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
show 1 more comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.