Optimizer Chose Merge Join, I hinted Loop join and much better performance. Why?
Sorry, not sure how to describe it because the plans are so different.
On the second (AD2) I hinted a LOOP on the same join and got this much different execution plan. Maybe it is obvious what happened to the perfomance but I also know that total logical read went from 28,700,855 on AD1 to 552,114 on AD2
Can someone explain why this happened and the best way to fix it? Hinting gets the results needed short term but your stuck with this choice through unforseeable changes.
My general advice is to provide a schema (indexes, statistics and so on) and write the query in a such a way that the optimizer naturally picks a good plan. This makes maximal use of the optimizer's ability to react to future changes in e.g. the data distribution. It seems at least possible in this case that the database does not have optimal indexing or statistical information to enable the optimizer to make the right choices.
Even so, there are cases where the optimizer requires a nudge in the right direction. Instead of the join hint sledgehammer, I would probably use a FORCESEEK on Table10 to ensure the optimizer considers the benefit of a dynamic seek plan properly (presumably for an OR or IN predicate):
This lighter hint should correct the main problem, while still allowing the optimizer great freedom in other areas of the plan.