spaghettidba (6/21/2013)
Also, the plans don't look very similar.The DELETE plan is way different. It's a parallel plan, while the MERGE plan is serial. There's a hash match and... it's a different plan!
I think most of the magic is in there.
Interesting. That goes well with something I've found: if you make the query too complex, the optimizer has trouble creating an optimal execution plan. If that happens, it is better to break your query up into multiple steps.
Thank you for your insight. 😎