缺失索引每個(gè)表只取最重要的缺失索引查找索創(chuàng)建索引
發(fā)布時(shí)間：2026/1/18 20:25:29 作者：Admin 閱讀：16
廣告：
-- 檢查缺失索引是否與現(xiàn)有索引沖突
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 [預(yù)計(jì)提升%],
ei.index_name as [現(xiàn)有索引],
ei.key_columns as [現(xiàn)有索引鍵列],
ei.included_columns as [現(xiàn)有索引包含列],
CASE
WHEN ei.key_columns LIKE '%' + REPLACE(REPLACE(mi.equality_columns, '[', ''), ']', '') + '%'
THEN '可能重復(fù)'
WHEN mi.equality_columns LIKE '%' + REPLACE(REPLACE(ei.key_columns, '[', ''), ']', '') + '%'
THEN '現(xiàn)有索引可能已覆蓋'
ELSE '需要?jiǎng)?chuàng)建'
END as [建議]
FROM MissingIndexes mi
LEFT JOIN ExistingIndexes ei ON mi.object_id = ei.object_id
WHERE mi.rn = 1 -- 每個(gè)表只取最重要的缺失索引
ORDER BY mi.avg_user_impact DESC;
這是一篇受密碼保護(hù)的文章，請(qǐng)輸入密碼進(jìn)行訪問：
廣告：
相關(guān)文章
