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.
UPDATE
is if the table has a self referencing foreign key or accesses UDFs that do data access or aren't schema bound