EAGER Spools, Lock Escalations, and Optional Parameters

The presence of an [[Most Common Icons, Their Meanings, and Performance Implications|Eager Spool]] in a SQL Server query execution plan should always be concerning.  Essentially this is telling you that the optimizer wants a read-consistent view of the data, which might be normal if seen in the query plan for an UPDATE statement, but should raise an eyebrow in most other cases.  More importantly, an Eager Spool is a [[Blocking and non-blocking Operators|blocking operator]], which means that "branch" of the query plan will be "blocked" from continuing until the Eager Spool is satisfied.  

I've written about why this occurs in an UPDATE statement [[Eager Spools in Query Plans and the Halloween Problem|here]].  But this blog post is about some situations where an Eager Spool can occur outside of an UPDATE statement.  

I struggled to come up with a repro script to reproduce an Eager Spool that would show the undesirable side effects of an Eager Spool so I decided to just show you a real world example that I found in the past and documented.   The "setup" is a large stored proc with lots of "[[Optional SQL Parameters and Performance|optional parameters]]" which are performance-draining to start with.  Here is the code:

AND (
(
@EncProvObjIds is not null
AND EXISTS ( SELECT 1 FROM dbo.Enc
WHERE RcvGrp.EncObjId = Enc.ObjId
AND Enc.EncProvObjId in (SELECT EncProvObjIdsTable.ObjId FROM @EncProvObjIdsTable EncProvObjIdsTable) )
)
OR @EncProvObjIds is null
)

Here we see that we only want to evaluate the EXISTS clause if we have valued @EncProvObjIds.  This is a standard "optional parameter" pattern that I've seen with almost every large database application I've worked with.  Here is the query plan for just this section of code:

That indicates the Index Scan on EncEncProvFX5 (an index on a HUGE table) is occurring before it even checks if @EncProvObjIds was valued!!!  The Eager Spool indicates that that branch of the query "blocks" until the condition is confirmed, since it's the right most operator of that branch it's scanning everything in the index/table.  It is also attempting to get a read-consistent view of the data (else it would be Lazy Spool).  No wonder it causes lock escalations...it's scanning a whole index when it doesn't have to, if it would just read the damn param first!!!!  I tried a few things...@EncProvObjIds to #TempTable, OPTION RECOMPILE, passing in lots of values in the optional parameter vs no values, setting the param to a local variable to avoid [[Problems with Parameter Sniffing|parameter sniffing]], even adding an OPTIMIZE FOR clause...nothing worked, always EAGER SPOOL.  

I eventually rewrote it like this...

JOIN dbo.Enc
ON RcvGrp.EncObjId = Enc.ObjId
LEFT JOIN #EncProvObjIdsTable EncProvObjIdsTable
ON Enc.EncProvObjId = EncProvObjIdsTable.ObjId
WHERE COALESCE(EncProvObjIdsTable.ObjId,'') = CASE WHEN @EncProvObjIds IS NOT NULL THEN Enc.EncProvObjId ELSE COALESCE(EncProvObjIdsTable.ObjId,'') END

...which generated this query plan:

The only way to get the Eager Spool to disappear was by converting the EXISTS to a standard JOIN.  Granted, this *trick* may not work in every situation, in fact, that may be a given.  The fix depends on your data and the cardinality of the child table.  Regardless, the solution is not what is important here, it is important to understand what an Eager Spool is and how performance-draining they can be.  BTW, performance increased 2 orders of magnitude with JUST THIS CHANGE.