Allow override of row estimations

Allow override of row estimations by Dave_Ballantyne

Active

Type:	Suggestion
ID:	775572
Opened:	1/2/2013 4:23:58 AM
Access Restriction:	Public

Workaround(s)

view

In my experience a 'bad' plan will often involve poor row estimations.
Sometimes there are techniques to improve that and sometimes not.

I would suggest that a new query hint could be introduced to allow a developer to directly influence the optimizer to set the expected number of rows from a logical join.

For instance

Select X.* , Y.* from X JOIN Y on X.ID = Y.ID (EXPECTEDROWS=100)

Would inform the optimizer that the developer expects the join to result in 100 rows.

This would allow for more stable and optimal plans when the optimizer 'gets it wrong'

Details (expand)

English

SQL Engine

As above

Improved Performance

As above

0 attachments

Posted by Microsoft on 4/29/2013 at 2:40 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 1/16/2013 at 2:41 PM

This is good feedback, we will review this for future releases.

Posted by Martin Smith on 1/3/2013 at 5:36 AM

Would the EXPECTEDROWS=100 be before or after any WHERE predicate on the tables?

I've often thought that something like this would be useful but the proposed implementation in the "ON" clause would obviously only work for joins rather than semi joins / anti semi joins.

Maybe SQL Server could generate an XML tree of its statistics estimates and we override the ones which need tuning in a "USE STATISTICS" hint analogous to "USE PLAN"