sys.dm_db_missing_index_group_stats - Usage & Examples

SQL Server's query optimizer maintains internal structures that track when it identifies potentially beneficial indexes during query compilation. When the optimizer generates execution plans, it evaluates whether additional indexes could improve performance through reduced key lookups, more efficient seeks, or better sort elimination. These evaluations occur in the Algebrizer and Optimization phases of query compilation within the SQL OS scheduler threads.

The missing index feature, introduced in SQL Server 2005 and enhanced in subsequent versions, creates statistical entries in dm_db_missing_index_group_stats when the optimizer's cost-based analysis determines an index could significantly reduce query cost. Each missing index group represents a unique combination of equality columns, inequality columns, and included columns that the optimizer has identified across multiple queries.

The DMV accumulates statistics differently across SQL Server versions. In SQL Server 2016 and earlier, the counters reset when the instance restarts or when the plan cache is cleared. SQL Server 2017 introduced improvements to missing index tracking accuracy, particularly for queries involving multiple tables. SQL Server 2019 enhanced the feature with better handling of filtered indexes and columnstore scenarios, while SQL Server 2022 improved tracking for queries with complex predicates and multiple column statistics.

The optimizer only suggests missing indexes for user queries, not system processes. Index suggestions accumulate regardless of whether queries use plan reuse or recompile, making the statistics reliable indicators of optimizer behavior patterns. However, the feature cannot suggest indexes for all scenarios, particularly those involving complex expressions, computed columns, or certain data types like xml or spatial.

-- Identify highest impact missing indexes by total user activity
SELECT 
    migs.group_handle,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    (migs.user_seeks + migs.user_scans) * migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) AS improvement_measure
FROM sys.dm_db_missing_index_group_stats migs
ORDER BY improvement_measure DESC;
-- Shows which missing index groups have highest potential performance impact

-- Get complete missing index details with table information
SELECT 
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig 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 mid.database_id = DB_ID()
ORDER BY (migs.user_seeks + migs.user_scans) * migs.avg_user_impact DESC;
-- Provides actionable index creation details for current database

-- Analyze missing index usage patterns over time
SELECT 
    OBJECT_NAME(mid.object_id) AS table_name,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.last_user_scan,
    DATEDIFF(day, migs.last_user_seek, GETDATE()) AS days_since_last_seek,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig 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 mid.database_id = DB_ID()
    AND migs.user_seeks > 0
ORDER BY migs.user_seeks DESC;
-- Identifies stale vs active missing index suggestions based on recent usage

-- Find missing indexes with high seek-to-scan ratios
SELECT 
    OBJECT_NAME(mid.object_id) AS table_name,
    migs.user_seeks,
    migs.user_scans,
    CASE WHEN migs.user_scans > 0 
         THEN CAST(migs.user_seeks AS FLOAT) / migs.user_scans 
         ELSE migs.user_seeks 
    END AS seek_to_scan_ratio,
    mid.equality_columns,
    mid.inequality_columns,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig 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 mid.database_id = DB_ID()
    AND (migs.user_seeks + migs.user_scans) > 100
ORDER BY seek_to_scan_ratio DESC;
-- Prioritizes indexes that would convert scans to more efficient seeks

Generate CREATE INDEX statements for top missing indexes

-- Generate index creation scripts for highest impact missing indexes
SELECT 
    'CREATE NONCLUSTERED INDEX [IX_' + 
    OBJECT_NAME(mid.object_id) + '_' + 
    REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''), '[', ''), ']', ''), ', ', '_') + 
    '] ON ' + 
    QUOTENAME(SCHEMA_NAME(OBJECTPROPERTY(mid.object_id, 'SchemaId'))) + '.' + 
    QUOTENAME(OBJECT_NAME(mid.object_id)) + 
    ' (' + ISNULL(mid.equality_columns, '') + 
    CASE WHEN mid.inequality_columns IS NOT NULL AND mid.equality_columns IS NOT NULL 
         THEN ', ' + mid.inequality_columns 
         ELSE ISNULL(mid.inequality_columns, '') 
    END + ')' +
    CASE WHEN mid.included_columns IS NOT NULL 
         THEN ' INCLUDE (' + mid.included_columns + ')' 
         ELSE '' 
    END + ' WITH (FILLFACTOR = 90, ONLINE = ON);' AS create_index_statement,
    (migs.user_seeks + migs.user_scans) * migs.avg_user_impact AS priority_score
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig 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 mid.database_id = DB_ID()
    AND (migs.user_seeks + migs.user_scans) > 50
    AND migs.avg_user_impact > 20
ORDER BY priority_score DESC;

Test in development first. Review generated index names for duplicates. Monitor disk space and maintenance window requirements. Expected impact: Reduced query execution time and CPU usage for affected queries.

Create indexes with compression for large tables

-- Create missing indexes with page compression for tables > 1GB
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = STRING_AGG(
    'CREATE NONCLUSTERED INDEX [IX_' + 
    OBJECT_NAME(mid.object_id) + '_Missing_' + 
    CAST(ROW_NUMBER() OVER (ORDER BY (migs.user_seeks + migs.user_scans) * migs.avg_user_impact DESC) AS VARCHAR(3)) + 
    '] ON ' + 
    QUOTENAME(OBJECT_NAME(mid.object_id)) + 
    ' (' + ISNULL(mid.equality_columns, '') + 
    CASE WHEN mid.inequality_columns IS NOT NULL AND mid.equality_columns IS NOT NULL 
         THEN ', ' + mid.inequality_columns 
         ELSE ISNULL(mid.inequality_columns, '') 
    END + ')' +
    CASE WHEN mid.included_columns IS NOT NULL 
         THEN ' INCLUDE (' + mid.included_columns + ')' 
         ELSE '' 
    END + 
    ' WITH (DATA_COMPRESSION = PAGE, FILLFACTOR = 85, ONLINE = ON);', CHAR(13) + CHAR(10))
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_partition_stats ps ON mid.object_id = ps.object_id
WHERE mid.database_id = DB_ID()
    AND ps.reserved_page_count > 128000  -- Tables > 1GB
    AND (migs.user_seeks + migs.user_scans) > 100
    AND migs.avg_user_impact > 30;

PRINT @sql;

Execute the printed statements individually during maintenance windows. Page compression reduces storage but increases CPU overhead. Expected impact: Significant performance improvement with reduced storage footprint for large tables.

Implement automated missing index monitoring with PowerShell or T-SQL Agent jobs that capture dm_db_missing_index_group_stats weekly and track trends. Avoid creating every suggested index, as this can lead to excessive maintenance overhead and diminishing returns.

Establish index creation thresholds: minimum 100 user seeks/scans, minimum 25% average user impact, and active usage within the last 30 days. Review missing index suggestions quarterly during maintenance planning cycles rather than reactively implementing them.

Configure Query Store in SQL Server 2016+ to correlate missing index suggestions with actual query performance metrics. This provides validation that suggested indexes deliver expected improvements. Monitor index usage statistics post-creation using sys.dm_db_index_usage_stats to identify and remove unused indexes.

Design table schemas with indexing strategies from the beginning. Include covering indexes in initial table designs based on known query patterns. Implement index maintenance strategies that account for the total index count per table, keeping it under 10 non-clustered indexes for OLTP workloads to minimize insert/update/delete overhead.

Use Database Engine Tuning Advisor sparingly and validate its recommendations against missing index DMVs. The advisor may suggest overlapping or redundant indexes that missing index DMVs handle more efficiently through column combination analysis.