Friday, March 5, 2010

Prevent Duplicate Indexes Due To Includes

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_seeksseeks ,
        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(281), 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 (VARCHARmig.index_group_handle) + '_'
        
CONVERT (VARCHARmid.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(281), 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%'

0 comments: