Sql Server: Query to View a list of missing indexes from your Sql Server database

Most companies will have a fair amount of SQL databases and its likely that most of those databases are performing sub-optimally due to missing indexes. We can debate (for a long time) the pros and cons of indexes, but the undeniable reality is that having missing indexes on large tables create a lot of issues in production environments (including, slowness, over spend on hardware and even outages). So how do you get a sense of how good or bad a database is? As luck would have it, Microsoft have a number of dynamic views that store the data you are looking for.

View missing index suggestions in DMVs

You can retrieve information about missing indexes by querying the dynamic management objects (DMVs). The following query uses the missing index DMVs to generate a series of CREATE INDEX statements. The index creation statements can be used to help you run the relevant DDL, once you have review all the output.

    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1), 
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) 
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' + 
        CONVERT (varchar, mig.index_group_handle) + '_' + 
        CONVERT (varchar, mid.index_handle) + ' ON ' + 
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') + 
            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
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON 
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON 
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;

Leave a Reply

Your email address will not be published. Required fields are marked *