sys.dm_db_index_usage_stats:索引使用統計 (user_seeks, scans, lookups, updates)。sys.dm_db_missing_index_*:最佳化工具建議的缺失索引。sys.indexes、sys.objects、sys.partitions:結構中繼資料。DECLARE @dbid INT = DB_ID();
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
i.index_id,
i.type_desc
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
AND i.index_id NOT IN (
SELECT s.index_id
FROM sys.dm_db_index_usage_stats s
WHERE s.object_id = i.object_id
AND s.index_id = i.index_id
AND s.database_id = @dbid
)
ORDER BY TableName, i.index_id;
處理:確認無隱性用途 (FK 強制、查詢提示) 後,評估 DROP INDEX。
SELECT TOP 50
ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), 0) AS TotalCost,
avg_user_impact,
statement AS TableName,
equality_columns,
inequality_columns,
included_columns,
user_seeks, user_scans
FROM sys.dm_db_missing_index_group_stats gs
JOIN sys.dm_db_missing_index_groups g ON gs.group_handle = g.index_group_handle
JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
WHERE statement LIKE '%YourTable%'
ORDER BY TotalCost DESC;
說明:equality_columns 適用於 =/IN,inequality_columns 適用於 >/</BETWEEN,included_columns 覆蓋 SELECT 列表避免 Key Lookup。
WITH IXC AS (
SELECT ic.object_id, ic.index_id, ic.index_column_id, ic.is_descending_key, ic.is_included_column, c.name AS column_name
FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
)
SELECT TOP 50
o.name AS TableName,
i.name AS IndexName,
i.index_id,
ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
p.TableRows,
STUFF((SELECT N', ' + QUOTENAME(column_name) + CASE is_descending_key WHEN 1 THEN N' DESC' ELSE N'' END
FROM IXC WHERE object_id = i.object_id AND index_id = i.index_id AND is_included_column = 0
ORDER BY index_column_id FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS KeyColumns,
STUFF((SELECT N', ' + QUOTENAME(column_name)
FROM IXC WHERE object_id = i.object_id AND index_id = i.index_id AND is_included_column = 1
ORDER BY index_column_id FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS IncludedColumns,
N'DROP INDEX ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(OBJECT_NAME(i.object_id)) AS DropStatement
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.index_id = i.index_id AND ius.object_id = i.object_id
JOIN sys.objects o ON ius.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN (SELECT SUM(rows) AS TableRows, index_id, object_id FROM sys.partitions GROUP BY index_id, object_id) p
ON ius.index_id = p.index_id AND ius.object_id = p.object_id
WHERE OBJECTPROPERTY(ius.object_id, 'IsUserTable') = 1
AND ius.database_id = DB_ID()
AND i.type_desc = 'NONCLUSTERED'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (ius.user_seeks + ius.user_scans + ius.user_lookups) ASC;
ALTER INDEX idx ON tbl DISABLE; (叢集索引禁用會鎖表,謹慎)。INCLUDE 覆蓋。ALTER INDEX ... REBUILD/REORGANIZE,配合 sys.dm_db_index_physical_stats 判斷碎片率 (>30% REBUILD, 5-30% REORG)。