How does SS determine JOIN operator row estimates
I think that low number is causing problems upstream but i don't know how to influence it.
to follow up a bit, i see that statistics are involved somehow but i can't make the numbers in vector or density match.
Also i forced a LOOP join here:
INNER JOIN dbo.tblActualFRTrips at ON ar.DailyRunID = at.DailyRunID
INNER LOOP JOIN dbo.tblActualFRRouteStopTimes arst ON at.DailyTripID = arst.DailyTripID
and performance is much better. I hate to leave that in production code are their other options?
There are many steps involved in join cardinality estimation. The base table statistics are modified by predicates on those tables (seeks in your case). This operation results in a new, derived histogram (and other statistical information) for each join input. These derived statistics are only used during query plan compilation – they are not visible to end users.
The derived histogram steps from both sides generally will not match up value-wise, so linear interpolation is used to align the steps. Then, additional logic and assumptions are applied to determine a cardinality for the join result. Obviously there are many things that can go wrong here; join cardinality estimation is quite a hard problem.
Fixing the Cardinality Estimate
Where a poor cardinality estimate affects the rest of a large plan, the most reliable way to fix it is to materialize the result of the join (just the columns and rows needed later) in a temporary table. People seem to resist this use of temporary tables for some reason, but it is a sound technique.
Using INNER LOOP JOIN also forces the order of the joins to follow the textual order in the query, just as if you had also specified OPTION (FORCE ORDER), so be very careful with that. Forcing the order of the joins may produce a plan that works well today, but things change.
On the other hand, if your knowledge of the system and data means you know following the textual join order will always be a reasonable proposition, now and for always, the join hint is fine – though I would add the redundant OPTION (FORCE ORDER) hint to make that behaviour more obvious to future readers.
My preference would be the temporary table.