Removing the spools: is it possible with the CROSS JOIN included
Thanks for the assist and great tool SQLSentry folks.
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.