is it possible to reconcile the "Estimated Rows" value which the Query Optimizer provides in my actual execution plan?
In my particular case: Estimated Rows = 313, Actual Rows = 90
Using the following hints I can see three stats loaded:
But what's next?
313 to 90 is not that far off. The key to understanding the estimates is in the statistics. So, you have a query that was passed a particular value. You go and look at the statistics in use. You'll find that either you got an average of the statistics, usually reflected in the density, or you got the average number of rows for one of the steps in the histogram of the statistics.
What you have is close, so I wouldn't be at all surprised that it's just some disparity between the actual number of rows for the given value and the average number of rows for the step it has within the histogram.
If you were on SQL Server 2014, you can use a new extended event to capture exactly which cardinality estimate was used for each operator within the plan. Even that can usually be figured out by looking at the values used.
answered Feb 25, 2014 at 02:56 PM
Grant Fritchey ♦♦
Update your statistics - give the Query Optimizer the latest metrics to work with.
answered Feb 25, 2014 at 02:28 PM