Why does the sort input/output 105 actual rows, but only estimate 1 output row?
The statistics are correct and have just had a FULLSCAN applied. I have been experimenting with generating dozens of additional statistics on filtered ranges, and a filtered index as well so that both sets are used… it has little impact.
But in particular I want to know about the one section in the top right. A constant scan puts out 105 rows. The sort knows it is getting 105 rows. It's not a distinct sort or anything unusual. But it then estimates it will output only 1 row (when it actually outputs all 105). Why?
The estimates are modified to reflect the "row goal" established by the TOP (5000). Essentially, the plan estimates show how many rows the optimizer expects to have to process at each iterator in order to produce the requested 5,000 rows – not the whole potential set.
More details:
http://sqlblog.com/blogs/paul_white/archive/2010/08/18/inside-the-optimiser-row-goals-in-depth.aspx (general details)
http://sqlblog.com/blogs/paul_white/archive/2010/08/22/row-goals-and-grouping.aspx (explains the Flow Distinct, in particular)
Row goals can be disabled using documented trace flag 4138.
http://support.microsoft.com/kb/2667211
In this case, the inaccurate estimations means that all rows are read in trying to reach the 5,000 goal, not just the 1 row the optimizer expected.