Sort row count estimate different from the row count of its source

Dan Holmes 2013-05-16 17:45:03

nodeid 79 shows a row estimate of 196 but the HASH source shows an estimated row count of 2100 as the sorts source. How can the sort have a different row count than its source? here is a quick screenshot of that section of the attached plan.

alt text
The same SQL except changing

LEFT JOIN dbo.v_FRSubRouteCal FRRoutePatterns ON FRRoutePatterns.FRSubRouteID = FRTripActuals.FRSubRouteID
AND FRRoutePatterns.CalendarDate = FRTripActuals.TripDate

LEFT JOIN dbo.v_FRTripStopCal FRTripStops ON FRTripStops.FRTripsPropertiesFRRouteStopID = FRStopActuals.FRTripsPropertiesFRRouteStopID
AND FRTripStops.CalendarDate = FRRunActuals.TripDate

To (from LEFT join to INNER)

JOIN dbo.v_FRSubRouteCal FRRoutePatterns ON FRRoutePatterns.FRSubRouteID = FRTripActuals.FRSubRouteID
AND FRRoutePatterns.CalendarDate = FRTripActuals.TripDate

JOIN dbo.v_FRTripStopCal FRTripStops ON FRTripStops.FRTripsPropertiesFRRouteStopID = FRStopActuals.FRTripsPropertiesFRRouteStopID
AND FRTripStops.CalendarDate = FRRunActuals.TripDate

produces a similar plan as this one but the row count of this operator is 2100. And this plan never finishes in Actual which is why these are estimated plans.

SQLkiwi 2013-05-20 22:56:29
1. A sort needs to process its entire input before it can return the first row, so the input estimate is always the full cardinality.

  1. The sort appears below a Top (1) so the optimizer applies a row goal. It estimates it will need to process 196 rows at the sort output in order to produce the first row from the Top iterator.