Why nested loop instead of more efficient merge join?

Betrando 2017-01-30 15:28:17

I migrated this join from SQL-Server 2008R2 to 2014SP2 with compatibility level 120. On the old server it used a merge join (266 reads), on the new one a nested loop (68632 reads). The execution time is the same, no matter if i use merge join or nested loop.
How can i optimize this query beside using query hints to force it to use a merge join?
I already tried providing a new index perfectly mathing the needs of object2 but no use.

Matak 2017-01-30 23:12:23
Your statistics are totally out of whack. Try updating them and rerunning.
Aaron Bertrand 2017-02-03 17:50:07
If the execution time is the same, why do you need to force a merge join, and why do you think that is more efficient? Using what metric?
Rob Farley 2017-02-05 07:53:15
I'm curious about the plan that uses the Merge Join – I wonder if there's more to it.

For example, as a Merge Join needs ordered data, they can sometimes push data through expensive Sort operations.

A Merge Join can run in 1-to-many or many-to-many mode, and the latter is way more expensive. Check the Residual Predicate property of the Merge Join too, to see if it's having to do more work than you'd expect.

As for your Nested Loop plan that you've attached, the inside Seek runs 34182 times and has 68632 reads. That's two reads per execution. This tells me that this index is only two levels deep, therefore there can't be many pages in the whole thing. Remember that when pages are already in RAM, reads are very cheap. I'm guessing there might be only dozens of pages, and that the true I/O effort here is a lot cheaper than your 68632 reads would suggest.

There was a time when I tuned a query from several minutes down to a few seconds, but in doing so, the reads went up from about 300 thousand to over 9 million. Based on the reads, it should've been 30x slower, but it was actually more than 30x faster.