The following, simple query in AdventureWorks:
SELECT * FROM Person.Person p JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
Gives the following execution plans:
If I look at the above plan, I can see the index scan and index seek both (correctly) estimate 290 rows, however, the estimated loops operator that joins the two, estimates 279 rows.
The old estimator also correctly guesses 290 rows out of both the seek and the scan but the nested loops estimates 289 rows which in the case of this query is a better estimate.
Is it true then that in the case of the new CE the optimizer estimates that when it is joins 290 rows from the index scan and 290 from the index seek, there will be 11 rows that do not match?
What method / formula does it use to make this estimate?
Am I correct in saying whatever said method is, that it has changed from the earlier CE version as that has made a different estimate?
I realise the "bad" estimate of the new CE is not significant enough to detriment performance, I am just trying to understand the estimators processing