sys.dm_db_missing_index_details - Usage & Examples

SQL Server's query optimizer generates missing index recommendations during the plan compilation phase when it encounters queries that would benefit from indexes that don't exist. The optimizer's cost-based analysis calculates potential performance improvements and stores these recommendations in the missing index infrastructure, which consists of four interconnected DMVs.

The missing index feature operates through the query processor's index selection logic. During plan compilation, the optimizer evaluates multiple access methods for each table reference. When it determines that a hypothetical index would significantly reduce query cost (typically by 10% or more), it records the recommendation in sys.dm_db_missing_index_details with supporting statistics in sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, and sys.dm_db_missing_index_columns.

The recommendation threshold varies by SQL Server version. SQL Server 2016 introduced improvements to missing index calculations for columnstore indexes and memory-optimized tables. SQL Server 2019 enhanced the accuracy of recommendations for queries with multiple table joins. SQL Server 2022 added intelligent query processing integration that refines recommendations based on actual execution patterns.

Missing index data accumulates from service startup and persists until the SQL Server service restarts or the database is detached. The system maintains up to 500 missing index groups per database, with older recommendations automatically purged when this limit is exceeded.

The optimizer generates recommendations only for queries that actually execute, not for compiled but unused plans. This behavior ensures recommendations reflect real workload patterns rather than theoretical scenarios.

-- Get top missing index recommendations with impact metrics
SELECT 
    d.database_id,
    d.object_id,
    OBJECT_NAME(d.object_id, d.database_id) AS table_name,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    gs.user_seeks,
    gs.user_scans,
    gs.avg_total_user_cost,
    gs.avg_user_impact,
    (gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans)) AS improvement_measure
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats gs ON g.index_group_handle = gs.group_handle
WHERE d.database_id = DB_ID()
ORDER BY improvement_measure DESC;

-- Analyze missing index columns for specific table
SELECT 
    d.statement AS table_name,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    gs.user_seeks,
    gs.last_user_seek,
    gs.avg_total_user_cost,
    gs.avg_user_impact
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats gs ON g.index_group_handle = gs.group_handle
WHERE d.database_id = DB_ID()
    AND OBJECT_NAME(d.object_id, d.database_id) = 'YourTableName'
ORDER BY gs.avg_user_impact DESC;

-- Check for overlapping missing index recommendations
WITH missing_indexes AS (
    SELECT 
        d.object_id,
        OBJECT_NAME(d.object_id, d.database_id) AS table_name,
        d.equality_columns,
        d.inequality_columns,
        d.included_columns,
        gs.avg_user_impact,
        ROW_NUMBER() OVER (PARTITION BY d.object_id ORDER BY gs.avg_user_impact DESC) AS rn
    FROM sys.dm_db_missing_index_details d
    INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
    INNER JOIN sys.dm_db_missing_index_group_stats gs ON g.index_group_handle = gs.group_handle
    WHERE d.database_id = DB_ID()
)
SELECT 
    table_name,
    equality_columns,
    inequality_columns,
    included_columns,
    avg_user_impact
FROM missing_indexes
WHERE object_id IN (
    SELECT object_id 
    FROM missing_indexes 
    GROUP BY object_id 
    HAVING COUNT(*) > 1
)
ORDER BY table_name, avg_user_impact DESC;

-- Generate CREATE INDEX statements with proper syntax
SELECT 
    'CREATE NONCLUSTERED INDEX [IX_' + OBJECT_NAME(d.object_id, d.database_id) + '_' 
    + CAST(ROW_NUMBER() OVER (ORDER BY gs.avg_user_impact DESC) AS VARCHAR(3)) + ']'
    + ' ON [' + DB_NAME(d.database_id) + '].[' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(d.object_id, d.database_id) + ']'
    + ' (' + ISNULL(d.equality_columns, '') 
    + CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ', ' ELSE '' END
    + ISNULL(d.inequality_columns, '') + ')'
    + ISNULL(' INCLUDE (' + d.included_columns + ')', '') + ';' AS create_index_statement,
    gs.avg_user_impact,
    gs.user_seeks + gs.user_scans AS usage_count
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats gs ON g.index_group_handle = gs.group_handle
INNER JOIN sys.objects o ON d.object_id = o.object_id
WHERE d.database_id = DB_ID()
    AND gs.avg_user_impact > 25
ORDER BY gs.avg_user_impact DESC;

High-Impact Missing Index Creation Creates indexes for recommendations with significant performance impact. This script generates and optionally executes CREATE INDEX statements for missing indexes with user impact above 50%.

-- Generate and create high-impact missing indexes
DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @execute BIT = 0; -- Set to 1 to execute, 0 to generate only

SELECT @sql = @sql + 
    'CREATE NONCLUSTERED INDEX [IX_' + OBJECT_NAME(d.object_id, d.database_id) + '_Missing_' 
    + CAST(ROW_NUMBER() OVER (ORDER BY gs.avg_user_impact DESC) AS VARCHAR(3)) + ']'
    + ' ON [' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(d.object_id, d.database_id) + ']'
    + ' (' + ISNULL(d.equality_columns, '') 
    + CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ', ' ELSE '' END
    + ISNULL(d.inequality_columns, '') + ')'
    + ISNULL(' INCLUDE (' + d.included_columns + ')', '') 
    + ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);' + CHAR(13) + CHAR(10)
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats gs ON g.index_group_handle = gs.group_handle
INNER JOIN sys.objects o ON d.object_id = o.object_id
WHERE d.database_id = DB_ID()
    AND gs.avg_user_impact > 50
    AND gs.user_seeks + gs.user_scans > 100;

PRINT @sql;

IF @execute = 1
    EXEC sp_executesql @sql;

Batch Index Creation with Error Handling Creates multiple missing indexes with proper error handling and logging. Test thoroughly in development environments before production deployment.

-- Create missing indexes with error handling and progress tracking
DECLARE @IndexSQL NVARCHAR(MAX);
DECLARE @TableName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @ErrorCount INT = 0;

DECLARE missing_index_cursor CURSOR FOR
SELECT 
    'CREATE NONCLUSTERED INDEX [IX_' + OBJECT_NAME(d.object_id, d.database_id) + '_Auto_' 
    + FORMAT(GETDATE(), 'yyyyMMdd') + '_' + CAST(ROW_NUMBER() OVER (ORDER BY gs.avg_user_impact DESC) AS VARCHAR(3)) + ']'
    + ' ON [' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(d.object_id, d.database_id) + ']'
    + ' (' + ISNULL(d.equality_columns, '') 
    + CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ', ' ELSE '' END
    + ISNULL(d.inequality_columns, '') + ')'
    + ISNULL(' INCLUDE (' + d.included_columns + ')', '') 
    + ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 4);' AS create_statement,
    OBJECT_NAME(d.object_id, d.database_id) AS table_name
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats gs ON g.index_group_handle = gs.group_handle
INNER JOIN sys.objects o ON d.object_id = o.object_id
WHERE d.database_id = DB_ID()
    AND gs.avg_user_impact > 30;

OPEN missing_index_cursor;
FETCH NEXT FROM missing_index_cursor INTO @IndexSQL, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        PRINT 'Creating index on table: ' + @TableName;
        EXEC sp_executesql @IndexSQL;
        PRINT 'Success: ' + @TableName;
    END TRY
    BEGIN CATCH
        SET @ErrorCount = @ErrorCount + 1;
        PRINT 'Error on ' + @TableName + ': ' + ERROR_MESSAGE();
    END CATCH
    
    FETCH NEXT FROM missing_index_cursor INTO @IndexSQL, @TableName;
END

CLOSE missing_index_cursor;
DEALLOCATE missing_index_cursor;

PRINT 'Index creation completed. Errors: ' + CAST(@ErrorCount AS VARCHAR(10));

Implement automated missing index monitoring through SQL Server Agent jobs that capture and analyze recommendations weekly. Create baseline measurements before implementing recommendations to validate actual performance improvements. The missing index DMVs provide estimates, not guarantees of improvement.

Establish index creation standards that consolidate overlapping recommendations. Multiple missing index entries for the same table often indicate opportunities to create composite indexes that satisfy multiple query patterns. Review existing index coverage before creating new indexes to avoid redundancy and maintenance overhead.

Configure query store retention policies to correlate missing index recommendations with actual query performance over time. SQL Server 2019 and later versions provide enhanced integration between query store and missing index recommendations, enabling more accurate impact assessment.

Monitor index usage statistics post-creation using sys.dm_db_index_usage_stats to identify underutilized indexes created from missing index recommendations. Remove unused indexes to reduce maintenance overhead and storage requirements. Implement quarterly index reviews to assess the ongoing value of automatically created indexes.

Set up alerting for databases with excessive missing index recommendations (over 100 per database), which may indicate poor initial index design or significant workload changes requiring comprehensive index strategy review.