您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2008 R2 尋找未使用索引的實戰腳本

編輯：關於SqlServer

精準識別「只寫不讀」的累贅索引

索引能加速查詢，但每筆 INSERT/UPDATE/DELETE 都需同步維護索引。若索引長期「user_updates 極大、但 user_seeks/scans/lookups 接近 0」，代表它白白消耗寫入效能卻未曾被查詢使用，應優先評估移除。

核心查詢腳本

SELECT TOP 1000
    o.name AS 表名,
    i.name AS 索引名,
    i.index_id AS 索引ID,
    dm_ius.user_seeks AS 搜尋次數,
    dm_ius.user_scans AS 掃描次數,
    dm_ius.user_lookups AS 書籤查找次數,
    dm_ius.user_updates AS 更新次數,
    p.TableRows AS 表行數,
    'DROP INDEX ' + QUOTENAME(i.name) 
    + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 刪除語句
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i 
    ON i.index_id = dm_ius.index_id 
   AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o 
    ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s 
    ON o.schema_id = s.schema_id
INNER JOIN (
    SELECT SUM(p.rows) AS TableRows, p.index_id, p.OBJECT_ID
    FROM sys.partitions p 
    GROUP BY p.index_id, p.OBJECT_ID
) p 
    ON p.index_id = dm_ius.index_id 
   AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID, 'IsUserTable') = 1
  AND dm_ius.database_id = DB_ID()
  AND i.type_desc = 'nonclustered'
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND o.name = 'tblorders'  -- 請依實際需求修改或移除此行
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC;

結果解讀指南

指標組合	含義	建議動作
user_updates 高、seeks/scans/lookups = 0	純寫入負擔，零查詢效益	強力候選刪除
user_updates 高、seeks/scans/lookups 低	維護成本遠大於查詢收益	評估查詢模式，確認可移除
所有計數皆低	冷資料表或新建索引未被使用	觀察一段時間再決定

⚠️ 關鍵注意事項

DMV 重置機制：sys.dm_db_index_usage_stats 隨 SQL Server 服務重啟歸零，務必在「代表性工作負載運行足夠長時間」後再跑此腳本。
系統索引排除：腳本已過濾 PK、Unique Constraint、Clustered Index，避免誤刪關鍵約束。
參數化查詢陷阱：某些查詢可能因參數化導致計畫未顯示 Index Seek，建議搭配 sys.dm_exec_query_stats 交叉驗證。
外鍵索引：外鍵欄位上的索引雖少被直接查詢，但對 CASCADE DELETE/UPDATE 效能至關重要，刪除前請確認無 FK 依賴。