A query that reads from SQL Server’s missing index DMVs and auto-generates ready-to-run CREATE INDEX statements. Results are ranked by impact score so you know where to focus first.

What it does

• Reads from sys.dm_db_missing_index_details and related DMVs
• Consolidates suggestions per table to avoid index sprawl
• Generates CREATE INDEX statements with DATA_COMPRESSION = PAGE
• Flags results as HIGH PRIORITY, RECOMMENDED, or NOTE: Low usage pattern

The Query

WITH MissingIndexes
AS (SELECT
               DB_NAME (dm_mid.database_id)                                                                          AS DatabaseName
             , dm_mid.database_id                                                                                    AS DatabaseID
             , OBJECT_SCHEMA_NAME (dm_mid.object_id, dm_mid.database_id)                                             AS SchemaName
             , OBJECT_NAME (dm_mid.object_id, dm_mid.database_id)                                                    AS TableName
             , dm_migs.avg_user_impact
             , dm_migs.user_seeks
             , dm_migs.user_scans
             , dm_migs.last_user_seek
             , dm_migs.last_user_scan
             , CONVERT (DECIMAL(18, 2), dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) / 100.0) AS impact_score
             , dm_migs.user_seeks + dm_migs.user_scans                                                               AS total_reads
             , ISNULL (dm_mid.equality_columns, '')                                                                  AS equality_columns
             , ISNULL (dm_mid.inequality_columns, '')                                                                AS inequality_columns
             , ISNULL (dm_mid.included_columns, '')                                                                  AS included_columns
             , dm_mid.statement
             , ROW_NUMBER () OVER (PARTITION BY dm_mid.database_id, dm_mid.object_id
                                   ORDER BY dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) DESC
                                  )                                                                                  AS rank_in_table
             , CONVERT (VARCHAR(10), ABS (CHECKSUM (
                   ISNULL (dm_mid.equality_columns, '')
                 + ISNULL (dm_mid.inequality_columns, '')
                 + ISNULL (dm_mid.included_columns, ''))) % 1000000000)                                              AS index_suffix
    FROM       sys.dm_db_missing_index_groups      dm_mig
    INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details     dm_mid  ON dm_mig.index_handle   = dm_mid.index_handle
    INNER JOIN sys.databases                       d       ON d.database_id          = dm_mid.database_id
    WHERE      d.is_read_only = 0 AND d.state_desc = 'ONLINE')
   , ConsolidatedIndexes
AS (SELECT
             DatabaseName, DatabaseID, SchemaName, TableName
           , STRING_AGG (
                 CASE WHEN rank_in_table = 1 THEN
                      'CREATE INDEX [IX_MMC_' + TableName + '_' + CONVERT (VARCHAR(10), index_suffix)
                    + '] ON ' + statement + ' ('
                    + CASE WHEN equality_columns   != '' THEN equality_columns ELSE '' END
                    + CASE WHEN inequality_columns != '' AND equality_columns != '' THEN ', ' + inequality_columns
                           WHEN inequality_columns != '' THEN inequality_columns ELSE '' END + ')'
                    + CASE WHEN included_columns   != '' THEN ' INCLUDE (' + included_columns + ')' ELSE '' END
                    + ' WITH (DATA_COMPRESSION = PAGE)'
                 END, CHAR(13)) AS consolidated_create_statement
           , SUM (impact_score)     AS total_impact_score
           , MAX (last_user_seek)   AS latest_seek
           , SUM (total_reads)      AS total_reads
           , COUNT (*)              AS suggested_index_count
           , MAX (equality_columns) AS key_columns
           , MAX (included_columns) AS included_columns
    FROM     MissingIndexes
    WHERE    rank_in_table = 1
    GROUP BY DatabaseName, DatabaseID, SchemaName, TableName)
SELECT
         DatabaseName, DatabaseID, SchemaName, TableName
       , total_impact_score        AS consolidated_impact_score
       , total_reads
       , latest_seek
       , suggested_index_count     AS original_suggestion_count
       , key_columns               AS proposed_key_columns
       , included_columns          AS proposed_included_columns
       , consolidated_create_statement
       , CASE WHEN total_reads < 1000     THEN 'NOTE: Low usage pattern'
              WHEN total_impact_score > 5000 THEN 'HIGH PRIORITY'
              ELSE 'RECOMMENDED'
         END                       AS implementation_note
FROM     ConsolidatedIndexes
WHERE    consolidated_create_statement IS NOT NULL
ORDER BY implementation_note DESC, latest_seek, total_impact_score DESC;

Output columns

Notes

• Only queries ONLINE, writable databases
• Index names use the IX_MMC_ prefix — change to match your naming convention
• Always review and test in a non-production environment before running
• DMV data resets on SQL Server service restart