Thanks for all the help everyone, I realize there were and are limitations to how much you can help with the information being necessarily masked.

An interesting note, thanks to TheSQLGuru,...

CPU seemed fine I thought, but I'll take a closer look at that.

It's not spilling to tempdb (unless it can spill without showing up in the plan -- please tell...

Ok, I've attached the scrubbed execution plan.

I removed RTrim and the CASE statement just in case, and substituted a literal in place of the variable.

That leaves just columns' values, and left joins.

Before and after the...

It's not blocking, and the results are repeatable. I can't really get more detailed than the below sql, but it gives the gist. I was wondering if anyone had encountered...