Make optimizer estimations more accurate by using metadata - by Dave_Ballantyne

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 772232 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 11/26/2012 7:56:57 AM
Access Restriction Public


I had an issue where I was getting a bad row count estimation

I was inner joining from table A to table B ON A.StatusID = B.ID .

ID being the PK on table B and a trusted foreign key constraint existed.

Even though the join could be simplified out, the row estimation on the output of the join operator by ~50% of the input, even though the foreign key guranteed 100% of the rows would join successfully.

The use of traceflag 2301 also fixed this 'error' and gave a one-to-one estimation, but the optimizer logic should be enhanced to bring this guarenteed relationship into the the 'base' logic.

Sign in to post a comment.
Posted by Paul White NZ on 6/3/2013 at 11:48 PM
Not common enough? Many production databases use foreign keys (yes, really) and better cardinality estimation for joins across such a relationship would be of wide benefit. If you have plans to address the underlying issue in other ways, please say so. Otherwise, "Won't Fix" seems like a very curious decision indeed.
Posted by Microsoft on 4/30/2013 at 1:02 PM

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
Posted by Vishal [MSFT] on 12/19/2012 at 2:42 PM
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.


Posted by Paul White NZ on 12/5/2012 at 11:21 AM
Not in my view, no. It can improve c.e. in some cases, not in others.
Posted by Matija Lah on 12/3/2012 at 3:46 AM
Should trace flag 2301 be considered a workaround?
Posted by Paul White NZ on 12/1/2012 at 6:39 PM
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;