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

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.

avatar image By Dan Holmes 725 asked May 16, 2013 at 05:45 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first
  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.

  2. 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.

avatar image By SQLkiwi ♦ 6.6k answered May 20, 2013 at 10:56 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x631
x166
x26

asked: May 16, 2013 at 05:45 PM

Seen: 309 times

Last Updated: May 20, 2013 at 10:56 PM