Home Dashboard Directory Help
Search

Make optimizer estimations more accurate by using metadata by Dave_Ballantyne


Status: 

Closed
 as Won't Fix Help for as Won't Fix


24
0
Sign in
to vote
Type: Suggestion
ID: 772232
Opened: 11/26/2012 7:56:57 AM
Access Restriction: Public
1
Workaround(s)
view

Description

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.

Details
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
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
Posted by Microsoft 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.

Thanks
Vishal



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;
Sign in to post a workaround.
Posted by Dave_Ballantyne on 12/3/2012 at 4:48 AM
Traceflag 2301, can be used to enable the enhanced optimizer logic to improve the estimation