More on the Halloween Problem

I wrote a few weeks back about [[Eager Spools in Query Plans and the Halloween Problem]].  Three days after I wrote that Paul White put up a blog post about an ENTIRE SERIES he did on sqlperformance.com.  He is much more thorough and authoritative than I could ever hope to be.  In a nutshell, the Halloween Problem occurs when we read and write keys of a common index.  There are other causes as well.  

I just wanted to pull out a few gems that I either learned or found very important from Paul's work.  I encourage anyone who works on tweaking performance of TSQL to spend some time reading Paul's findings on the Halloween problem.  

• The Eager Spool essentially reads every row from the child operator and stores that information in a temporary structure before it proceeds to the next step.  Under memory pressure this means tempdb spills to disk.  
• One alternative is an index hint so we avoid using the index that requires halloween protection, ie the index keys are unstable.  
• A query plan requiring halloween protection will require more locks, and longer locks, then an alternative plan.  
• If an UPDATE statement modifies one of the keys of a composite index you may consider making that column an INCLUDED column instead.  
• Halloween Protection is required for INSERT statements where the target table is also referenced in the SELECT statement.  
• HP is required for DELETE statements with self-join relationships.  
• If your performance issue is an Eager Spool on an INSERT...WHERE NOT EXISTS (<in table already>) query (Paul calls this a "hole-filling query") you can try a MERGE statement instead.  Here are the rules:
  • WHEN NOT MATCHED BY TARGET clause must EXACTLY match the ON clause in the USING clause.  
  • the target must have a unique key
• You may see an Eager Spool in a query that uses a scalar function, even if that scalar function does not access any tables.  This happens because SQL Server must assume that any scalar function not declare with SCHEMABINDING may re-read from one of your tables, so SQL Server must protect from the HP.  The solution is to add SCHEMABINDING

Thanks to Paul White for his great articles on the Halloween Problem.