Why does the sort input/output 105 actual rows, but only estimate 1 output row?

Cody 2014-10-17 10:12:58

This plan has a bunch of problems including the wildly inaccurate row counts. Unfortunately the query can't be changed (whatsoever), and is executed with different text each time so can't even have a forced plan attached.

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?

/storage/attachments/1043-tmpB92C_Anonymized.queryanalysis
tjosh52 2014-10-17 13:24:41
I haven't looked at the plan yet but does the query call a UDF?
SQLkiwi 2014-10-17 12:11:56
>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.

Mikael Eriksson 2014-10-17 12:40:54
There is an estimated number of executions of 2.0 of the Index Seek on Table1.Index1. Does that make sense in some way when there is an estimate of 1 row coming from the sort?
SQLkiwi 2014-10-17 13:41:53
Mikael, not much, no. It might be a rounding error, or a consequence of having trace flag 8666 on (which it is), or one of a number of other causes. There's a limit to how much insight one can garner from an anonymized plan.
Mikael Eriksson 2014-10-17 14:03:16
Ok, thanks. Did not know about 8666. Something new to study then. Noticed that wszReason is heuristic for all ModTrackingInfo. Don't know what other values can be there but looks like no stats is used at all and estimates fall back on constant percent values estimates. Perhaps due to convert/functions on columns.
October 17, 2014
No Comments