x

Reconcile "Estimated Rows" Value?

Dear Friends,

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:

 OPTION
 (
     RECOMPILE,-- Used to see the Statistics Output
     QUERYTRACEON 3604,-- Redirects the output to SSMS
     QUERYTRACEON 9204 -- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
 )

But what's next?

Thanks!

more ▼

asked Feb 25, 2014 at 01:56 PM in Default

avatar image

eghetto
2.2k 18 24 31

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Feb 25, 2014 at 02:56 PM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

Thanks, Grant! 313 was actually the average number of rows for my histogram step (AVG_RANGE_ROWS). Simple as that. Currently we're not on a SQL Server 2014 - but it's an interesting hint for the future!

Feb 26, 2014 at 09:22 AM eghetto
(comments are locked)
10|1200 characters needed characters left

Update your statistics - give the Query Optimizer the latest metrics to work with.

more ▼

answered Feb 25, 2014 at 02:28 PM

avatar image

Blackhawk-17
12.1k 30 36 42

Thanks, Greg! That's what I did before the execution. The statistics were/are "fresh"...

Feb 26, 2014 at 06:44 AM eghetto
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x61
x47
x2
x2

asked: Feb 25, 2014 at 01:56 PM

Seen: 592 times

Last Updated: Feb 26, 2014 at 09:22 AM

Copyright 2017 Redgate Software. Privacy Policy