Right most node has the wrong estimate
WHERE at.TripDate >= @TimeRangeStart — @TimeRangeStart = '20130506'
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.
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 OPTION (RECOMPILE) to the statement would enable use of the filtered index, though you will only see it used in an actual plan, not an estimated one.
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:
SELECT COUNT_BIG(*) FROM [dbo].[tblActualFRTrips] WHERE 1 <> 2 AND TripDate >= '20130506';
The 1 <> 2 is there just to prevent simple parameterization (we want estimates for the specific value '20130506').
Add one or both of the residual predicates to the query to see how they affect the estimate.
[ScheduledStart] < dateadd(day,1,'20130507') [ScheduledEnd] >= '20130507'
You can also run the query for just one of those predicates to see the estimation for each one. You might find adding OPTION (RECOMPILE) to the query results in an acceptable estimate (because the optimizer sees constant-folded values instead of parameters). On the other hand, the estimate may still be too low because cardinality estimation often assumes predicates are independent (and they may well not be in this case).
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:
- Ensure statistics for all columns are up-to-date (especially ascending keys)
- Try adding a multi-column statistic to capture the correlations
- Ultimately, it may not be possible to get a good estimate, so consider materializing the result of the problematic query in a temporary table and start the query from there.