I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query is fairly straightforward, looking something like this:
INNER JOIN t2 ON t1.id = t2.t1_id
LEFT OUTER JOIN t3 ON t2.id = t3.t2_id
LEFT OUTER JOIN t4 ON t3.t4_id = t4.id
WHERE t4.id = some_GUID
Playing around with the query, I noticed that when I hint it to use a Merge join for one of the joins, it runs many times faster. This I can understand - Merge join is a better option for the data that is joined, but SQL Server just doesn't estimate it right choosing the Nested Loops.
What I don't fully understand is why does this join hint changes all the estimates for all the plan operators? From reading different articles and books, I assumed that the cardinality estimations are performed before the plan is built, so using a hint would not have changed the estimations, but rather explicitly tell SQL Server to use a particular physical join implementation.
What I see, however, is that Merge hint causes all estimations to become pretty much perfect. Why does this happen and are there any common techniques to make query optimizer make a better estimate without a hint - considering that statistics obviously allow for this?
UPD: anonymized execution plans can be found here: https://www.dropbox.com/s/hchfuru35qqj89s/merge_join.sqlplan?dl=0 https://www.dropbox.com/s/38sjtv0t7vjjfdp/no_hints_join.sqlplan?dl=0
I checked the stats used by both queries using TF 3604, 9292 and 9204, and those are identical. However indexes which are scanned/seeked differ between the queries.
Besides that, I tried running the query with
OPTION (FORCE ORDER) - it runs even faster than using merge join, choosing HASH MATCH for every join.