How to find out which index is missing

Published on: 19th Sep 2019

Updated on: 19th May 2021

Overview

The user reported a performance issue after the operation and it's difficult to find out what has happened during the slow down.

Usually, the checklist will be:

Goto the Event Viewer and check if there is any Windows update, schedule jobs are running or something crashing.
Review the application log (that was generated by your application) and see if you can find any clue.

After you have done the above checking but yet to find any clue, you may want to see if there is any missing index that caused the slow down.

The implementation

To find out the missing index, we have to analyze the information in the following system views:

sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_details

You may download the script that combine all the necessary information from the following URL:

https://gist.github.com/alexsorokoletov/a079629f9e1435c7f81f

And here is partial of the SQL script:

SELECT
    CONVERT (varchar, getdate(), 126) AS runtime,
    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,

    '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]

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
CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
--and database_id =  DB_ID('my_database')

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

How to use it?

I'm relying on the improvement_measure value and I review the top 5 missing index information before deciding which index should be created.

We should not create all the indexes returned by this query. Because some of the missing indexes can be merged into the existing index.

Naming convention

We want to know how many times that we have reviewed a particular index and our naming rule works this way,

IX_my_table_1 - this is the first index.
IX_my_table_2 - this is another index.
IX_my_table_2_1 - this is the newer version of index where IX_my_table_2 has been dropped and merged with the new missing columns.

Where

IX indicates it is an index.
my_table is the table name.
_99_99 is the index number and review number.

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.