However it is always giving an estimated row count of 1 when the actual value is 88. This is the relevant histogram.
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
2013-05-03 00:00:00.000 261 88 3 87
2013-05-06 00:00:00.000 0 87 0 1
2013-05-07 00:00:00.000 0 88 0 1
Based on the predicate and the histogram i would have expected an estimate of 175. There is a non-seek predicate but why would that reduce the number to 1? I have tried a filtered index too and that wasn't used even when i dropped this one.
By Dan Holmes 725 asked May 08, 2013 at 01:58 PM
A filtered index would not be used if the plan is cached for reuse (with different parameter values). This is logical, because the optimizer cannot guarantee the filtered index would be valid for different parameter values. Adding
The seek in question may well be estimated at around 175 rows, but the residual predicates will reduce that value, typically by multiplying the expected selectivities together. The cardinality estimate for the following query should be around 175 rows:
Add one or both of the residual predicates to the query to see how they affect the estimate.
You can also run the query for just one of those predicates to see the estimation for each one. You might find adding
For completeness, also try the query with trace flag 4199 on (this enables various plan-affecting optimizer and cardinality estimate bug fixes), and/or trace flag 2301. The latter flag is documented, and enables a more advanced treatment of estimates under some common scenarios.
Depending on the outcome of the experiments above, there may be other things we could try to get a better estimate - please let me know how you get on.
Summary from comments:
By SQLkiwi ♦ 6.6k answered May 08, 2013 at 03:51 PM