萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2005+ 索引評估與管理:DMV 實戰查詢未使用、缺失、低效索引

DMV 核心視圖

  • sys.dm_db_index_usage_stats:索引使用統計 (user_seeks, scans, lookups, updates)。
  • sys.dm_db_missing_index_*:最佳化工具建議的缺失索引。
  • sys.indexes、sys.objects、sys.partitions:結構中繼資料。

1. 找出從未被使用的索引

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。

2. 缺失索引建議 (Top 50 依總成本)

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。

3. 低使用率索引 (維護成本 > 查詢收益)

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;

判讀指標

  • user_updates 遠大於 seeks+scans+lookups:索引維護成本高、查詢效益低 → 候選刪除。
  • user_seeks=0, scans=0, lookups=0, updates>0:純寫入負擔,必刪。
  • TableRows 偏小 (<1000):掃描成本低,索引效益有限。

維護建議

  1. 定期 (週/月) 執行上述查詢,產出索引健檢報表。
  2. 刪除前先 停用索引 觀察一段時間:ALTER INDEX idx ON tbl DISABLE; (叢集索引禁用會鎖表,謹慎)。
  3. 缺失索引建議需人工審核:合併相似索引、避免過度索引、考慮 INCLUDE 覆蓋。
  4. 重建/重組碎片:ALTER INDEX ... REBUILD/REORGANIZE,配合 sys.dm_db_index_physical_stats 判斷碎片率 (>30% REBUILD, 5-30% REORG)。
copyright © 萬盛學電腦網 all rights reserved