缺失索引每个表只取最重要的缺失索引查找索创建索引

-- 检查缺失索引是否与现有索引冲突 WITH MissingIndexes AS ( SELECT mid.object_id, mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.avg_user_impact, ROW_NUMBER() OVER (PARTITION BY mid.object_id ORDER BY migs.avg_user_impact DESC) as rn FROM sys.dm_db_missing_index_group_stats migs JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle ), ExistingIndexes AS ( SELECT i.object_id, i.name as index_name, i.type_desc, i.is_unique, STUFF(( SELECT ', ' + c.name FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 ORDER BY ic.key_ordinal FOR XML PATH('') ), 1, 2, '') as key_columns, STUFF(( SELECT ', ' + c.name FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1 ORDER BY ic.index_column_id FOR XML PATH('') ), 1, 2, '') as included_columns FROM sys.indexes i WHERE i.type IN (1, 2) -- 聚集和非聚集索引 ) SELECT OBJECT_NAME(mi.object_id) as [表名], mi.equality_columns as [缺失索引-等值列], mi.inequality_columns as [缺失索引-范围列], mi.included_columns as [缺失索引-包含列], mi.avg_user_impact as [预计提升%], ei.index_name as [现有索引], ei.key_columns as [现有索引键列], ei.included_columns as [现有索引包含列], CASE WHEN ei.key_columns LIKE '%' + REPLACE(REPLACE(mi.equality_columns, '[', ''), ']', '') + '%' THEN '可能重复' WHEN mi.equality_columns LIKE '%' + REPLACE(REPLACE(ei.key_columns, '[', ''), ']', '') + '%' THEN '现有索引可能已覆盖' ELSE '需要创建' END as [建议] FROM MissingIndexes mi LEFT JOIN ExistingIndexes ei ON mi.object_id = ei.object_id WHERE mi.rn = 1 -- 每个表只取最重要的缺失索引 ORDER BY mi.avg_user_impact DESC;