Make optimizer estimations more accurate by using metadata by Dave_Ballantyne

Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenario reported in the bug are not common enough and due to the risk of implementing a fix it unfortunately does not meet the bar for the current version of the product.

If you have more supporting information and details or feel strongly that we should reconsider, please do resubmit this item for review.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager

Thanks for your feedback Paul. As you are aware our Cardnality estimation logic takes multiple scenarios/properties into consideration before we come up with an estimate. We keep working on making imrovements into our CE logic to allow optimal plan choices while minimizing any undesired plan changes.

We will track this request for future consideration.

Thanks
Vishal

Not in my view, no. It can improve c.e. in some cases, not in others.

Should trace flag 2301 be considered a workaround?

In case it is not clear from the text, the suggestion is to improve join cardinality estimation by accounting for PK-FK relationships, for example:

-- Join cardinality estimate is 92115.2
-- Ought to be 113443 (cardinality of TransactionHistory)
-- Every row is guaranteed to join exactly once
SELECT p.ProductID, th.ProductID
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID;