Estimate number of rows

Question

Estimate number of rows

manole_i

SSC Eights!

Points: 860
More actions
February 10, 2016 at 10:22 am

#307866

I got a question about how Estimated number of Rows is calculated for the query in next picture:
https://www.dropbox.com/s/iqxgrseagy6fm9y/plan.jpg?dl=0
I do not understand how 7929.2 is obtained.
Here is the result of dbcc show_statistics for the concerned index and value 45155:
https://www.dropbox.com/s/tcsj1n23tbvzvg5/dbcc.jpg?dl=0
My expectation was EQ_ROWS * AllDensity.
Any ideas?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There are two predicates, so the expected cardinality is multiplied together

If the first is expected to affect 50% of the rows and the second 20% of the rows, then the estimate for the two together is 10% of the rows.

I go into a fair amount of detail on how in this presentation: http://www.sqlpass.org/24hours/2014/summitpreview/Sessions/SessionDetails.aspx?sid=7279

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

manole_i SSC Eights! Points: 860 More actions · Answer 2

Ok, thank you for your reply. In my case is something like:

select (1487899.00 / 390500384.00) * (2081025.00 / 390500384.00) * 390500384.00

which is 7929.19839...~7929.2

MadAdmin SSChampion Points: 11261 More actions · Answer 3

P(A and B) = P(A) * P(B)

to put it simply

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]