0

I have used the database engine tuning advisor, DTA, in order to improve the performance of one of my databases.

It produced a list of recommendations, mostly multicolumn statistics.

These recommendations need then to be cross checked and tested, before applying them in production.

Statistics are used by the query optimiser (whitepaper on MSDN) to track distribution of values in indexes and/or columns.

Inspired by the following reading:

When is it better to create STATISTICS instead of creating an Index?

MY QUESTION IS

Other than the DTA, is there any other way to find out the missing multicolumn statistics?

  • 1
    Other than the DTA, is there any other way to find out the missing multicolumn statistics? Nope ! query optimizer assumes that columns within the same table are independent. Joe Sack talks about it here and @paulwhite talks about it here. – Kin Shah May 11 '16 at 16:55
2

Other than the DTA, is there any other way to find out the missing multicolumn statistics?

Nope ! query optimizer assumes that columns within the same table are independent. Joe Sack talks about it here

The query optimizer assumes that columns within the same table are independent. For example, if you have a city and state column, we may intuitively know that these two columns are correlated, but SQL Server does not understand this unless we help it out with an associated multi-column index, or with manually-created multi-column statistics. Without helping the optimizer with correlation, the selectivity of your predicates may be exaggerated.

and @paulwhite talks about it here.

A certain amount of trial-and-error may be necessary to identify the multi-column statistic.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.