SQL DBA Tools: Index Recommendations

Finding Missing Index Recommendations

SQL Server and Azure SQL continuously track queries that could benefit from additional indexes. This data is exposed through the sys.dm_db_missing_index DMVs and can be surfaced with the following query.

The improvement_measure column shows how much impact a given index will have. The user_seeks and last_user_seek columns show how often and when the index was needed.

The Query

SELECT
    improvement_measure =
        migs.avg_total_user_cost
        * (migs.avg_user_impact / 100.0)
        * (migs.user_seeks + migs.user_scans)
    , create_index_statement =
        '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 + ')', '')
    , 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

Key Columns Explained

• improvement_measure — A calculated score estimating the total benefit. Higher values indicate higher priority indexes.
• create_index_statement — A ready-to-run CREATE INDEX statement you can copy, review, and execute.
• user_seeks — Number of times a seek operation would have benefited from this index.
• user_scans — Number of times a scan operation would have used this index.
• avg_user_impact — Estimated percentage improvement if the index existed.

Important Caveats

The DMV data is based on query plans since the last SQL Server restart. Review each recommendation carefully before implementing — not every suggested index should be created. Duplicate or overlapping indexes increase write overhead and consume storage.

Datazip provides expert SQL Server performance tuning and 24/7 managed monitoring. Call 1-833-DATAZIP or visit www.SQLBrainBox.com for our monitoring tool.