here the 2 execution plans

the good plan is generated by sql 2008R2 (the same is generated with compatibility level 100 or by using the traceflag 9481)

again the query is really horrible and the plans are really hard to read because they are big.

I don't expect someone to have a magical trick or something like that, i already said to the people in charge that their query is horrible and i have proposed to them to rewrite it.

But anyway at constant query/database/server, going from a couple of seconds to many minutes sounds really strange. So if we can understand what is going on maybe it will help people to prevent it!

thanks,

nkl