merge join operator estimations
i always wonder if the tables estimated rows is equal to actual rows why joining operator come up with way off estimation. and how can we solve these kind of issues?
The optimizer has good auto-created statistics on the #templocations.id values, including a distribution histogram, so there is no problem there. The situation with tip.location values is slightly different.
Cardinality estimation uses statistics associated with the index to estimate the number of rows (and the value distribution) for the range of time_start values specified, giving an exactly estimate from the seek.
What the optimizer does not know, is what the distribution of location values within that range of time_start values is. It can make some assumptions based on the location table statistics, but there is more than one way to do this, and it represents an 'educated guess' at best.
You can find more details about how join cardinality is estimated in SQL Server 2012 (and the modified algorithms in SQL Server 2014) in a Microsoft White Paper I helped tech-review recently.
If the batch you posted in running within a stored procedure (or a function) you will also want to review Temporary Tables in Stored Procedures to see if you need to update statistics before using the temporary table and to include OPTION (RECOMPILE) on the statement that references it.
It also seems curious to use a join hint (INNER MERGE JOIN) here. Join hints come with an implicit FORCE ORDER, meaning the query optimizer can no longer consider reordering tables from the textual order as it normally would. Most often, this is unintended, and OPTION (MERGE JOIN) is preferable (this hint does not force order).
On the same topic, are you sure merge join is optimal here? You do not specify ordered results in an ORDER BY clause, so I assume the result order is unimportant, so that cannot be the (incorrect) motivation behind the join hint. Two sorts into a many-to-many merge join seems inferior to a loops join or hash join strategy. You should let the optimizer choose here.
If the join hint was introduced as a result of prior issues with parameter sniffing, you should still remove the join hint and use OPTION (RECOMPILE) instead.