@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...
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.
Dave Wentzel CONTENT
sql server performance