Eager Spools in Query Plans and the Halloween Problem

I've written twice in the past on Eager Spools in query plans ([[Most Common Icons, Their Meanings, and Performance Implications|here]] and [[EAGER Spools, Lock Escalations, and Optional Parameters|here]]).  An eager spool is a [[Blocking and non-blocking Operators|blocking operator]], which means it will have performance implications.  Please see my other blog posts for additional information.  

An eager spool is always used to maintain a read-consistent view of the data.  The easiest way to explain this is to note that an eager spool solves the "Halloween Problem".  The Halloween Problem has nothing to do with the holiday, it merely means that when updating a row the row may migrate to a new location that is later scanned again, causing rows to be returned more than they should be.  For instance, if you had an index on a Salary column and wrote a query to update every employee's salary by 10% if they made less than 100K, without a Eager Spool you may find employee salaries getting updated more than once.  

I wrote a post ([[EAGER Spools, Lock Escalations, and Optional Parameters]])where I tried to explain why you may see eager spools in SELECT statements and how to eliminate them.  The rewrite I settled on removed the Eager Spool and performance improved, but frankly the query is ugly and still didn't perform great.  Our customers are again complaining about performance on this query and it occurred to me that the ultimate cause was of course parameter sniffing and "optional parametering" (discussed [[How to Make a Stored Procedure Parameter Optional|here]]).  The simplest thing to do was to remove the optional parametering and copy/paste the query with and without the optional parameter code block in separate IF blocks.  Performance is now very good, regardless of what parameters are used for invocation.  Lesson learned...the simplest fix is usually the best.  

[[More on the Halloween Problem]]