How to list the missing multicolumn statistics?

Question

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?

Answer 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

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.