Removing the spools: is it possible with the CROSS JOIN included

Dan Holmes 2013-04-03 15:01:27

Attached are two plans with the same output: One retrieves settings on the fly from tables and the has them hardcoded into the CASE expression. For the dynamic one, three spools are created in the plan which bloats the reads to 2mil+. The constant setting version doesn't have this issue. What i would like to know is if there is any query variation that would allow the dynamic setting retrieval but without the high read count? I have rewritten it everyway i can think of but can't get the spools out. thx for any tips.

link text

link text

Dan Holmes 2013-04-04 20:56:58
couple of new/changed indexes based on the pull from the spool and i now have them all removed. Down to 300k reads and a full second faster.

Thanks for the assist and great tool SQLSentry folks.

SQLkiwi 2013-04-19 03:36:17
Marked as the answer to this question. It is often possible to remove a spool (especially an eager index spool) by implementing the on-the-fly index. Plan properties can show you the index keys and any included columns (seek keys and output columns).
Jonathan Kehayias 2013-04-03 16:23:34
Hey Dan,

What I immediately notice is that the dynamic plan goes parallel, so does hinting OPTION(MAXDOP 1) for it prevent the spools? However, looking at the TextData you have different CASE logic, which is what I guess you were explaining above but it's unclear to me whether you are saying that

CASE WHEN CAST(service_miles_include_deadhead.settingvalue AS INT) = 0 OR CAST(ignore_garage_pullin_pullout.settingvalue AS INT) = 1

is supposed to be logically equivalent result wise to:

CASE WHEN 0 = 0 OR 1 = 1

based on the data expected in the table. The difference in case statement here is significant for optimization and processing.

Dan Holmes 2013-04-03 16:33:04
MAXDOP 1 doesn't change the spool operation. In fact the plan looks the same except for the parallel operators.

As for the CASE expression, yes the values are logically equivalent. I left the expression for the hardcoded variant to keep the syntax similar. I changed the "0=0" to "@i = 0" where @i is 0 and the plan remained the same.

The table tblSetting has a UNIQUE constraint on the internalid column so i was expecting the optimizer to get the value once and apply it to all rows instead of getting it for every row kinda like the way non-correlated subqueries can work.