Find Missing Indexes in a Database

Find Missing Indexes in a Database

 

Here is the SQL SCRIPT that can be helpful identifying missing indexes in a database. This query identifies missing indexes based on query cost benefit. I have used this script and found very useful. Again do not believe blindly on the output of this script and use your common sense before taking any decision.

Best option is to analyze the execution plan before creating any indexes as per output from the above script. You need to make sure that you do not create any unused indexes in your database. Remember having unused indexes affects the performance of the database. Also this script displays the missing indexes only when the query improvement cost is more than 10.

ALWAYS remember whenever the SQL instance gets restarted, data from dynamic management views get flushed out.

 

 

Here is the SCRIPT:

-- Identifying missing indexes based on query cost benefit.

SELECT 

            migs.avg_total_user_cost *
          ( migs.avg_user_impact / 100.0 )*
          ( migs.user_seeks + migs.user_scans )

          AS Query_Cost_Benefit ,

        'CREATE INDEX [missing_index_'

        + CONVERT (VARCHAR, mig.index_group_handle) + '_'
        + CONVERT (VARCHAR, mid.index_handle) + '_'
        + LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' 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]

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   migs.avg_total_user_cost *

            ( migs.avg_user_impact / 100.0 )*
            ( migs.user_seeks + migs.user_scans ) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans ) DESC