pictures
There's not a lot to go off of in the pictures of query plans you posted, which share many similarities in shape and join order. In fact, they're identical aside from estimates and costs. The bad plan has all one row estimates, and the good plan does a bit better.
What I think you're running into is either the ascending key problem, because
Stats are repopulated every Monday. Today (Thursday) the plan is bad
Or temp table caching is leading you astray. You'll wanna check out these articles for more detail there;
inference
What I see happen quite a bit with the ascending key problem and the legacy cardinality estimator is the one row guess from off-histogram values leads to poor plan choices. That's understandable, since one row guesses are rarely accurate.
A similar trick can also happen when temp tables are cached with histogram values for one execution that don't match up with subsequent executions. It's closer to parameter sniffing than the ascending key problem, but it leads to the same thing for you.
The position this puts you in is one where the attributes of the nested loops joins in your plans will show different values for being optimized and using some form of prefetching.
I've written about the performance problems this can lead to here:
You'll likely want to try updating stats on user tables more frequently, and as detailed in SQL Server Temporary Object Caching linked above:
- Manually UPDATE STATISTICS on the temporary table within the module; and
- Add an OPTION (RECOMPILE) hint to statements that reference the temporary table
