SQLServerPedia

Many times if you look for missing indexes you will often find a lot of duplication due to all the Include colums. For example, you will find a lot of entries like this.

CREATE INDEX ix... ON TableA (Col1) INCLUDE(Col2)
CREATE INDEX ix... ON TableA (Col1) INCLUDE(Col2,Col3)

Obviously, you don't need both of these indexes. The second one will work just fine. However, you will find reccomendations for both in the missing index DMV. What I have been doing lately is grouping by the base index and then reviewing the Include reccomendations seperately by using the following query.

SELECT  COUNT(*) CountBeforeInclude ,
        mid.statement ,
        SUM(migs.user_seeks) seeks ,
        mid.statement + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
                    AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')' AS base_index_statement ,
        SUM(CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
            * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans ))) AS improvement_measure

FROM    sys.dm_db_missing_index_groups mig
        INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

GROUP BY mid.statement ,
        mid.statement + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
                    AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')'

ORDER BY improvement_measure DESC

This helps out with a copule of things.

1. You can get aggregated data for the improvement measures.
2. It prevents you from creating redundant indexes.

Then you can take a single table and plug it into the following query to come up with your own index that covers multiple missing indexes. (Make sure to change the DatabaseName and TableName parameters in the WHERE clause.)

SELECT  mid.statement ,
        migs.user_seeks ,
        equality_columns ,
        inequality_columns ,
        included_columns ,
        'CREATE INDEX missing_index_'
        + CONVERT (VARCHAR, mig.index_group_handle) + '_'
        + CONVERT (VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' ('
        + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
                    AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
                                                              + mid.included_columns
                                                              + ')', '') AS create_index_statement ,
        migs.* ,
        mid.database_id ,
        mid.[object_id] ,
        mig.index_group_handle ,
        mid.index_handle ,
        CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
        * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans )) AS improvement_measure

FROM    sys.dm_db_missing_index_groups mig
        INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE   DB_NAME(database_id) = 'DatabaseName'
        AND mid.statement LIKE '%TableName%'