SQL Server 索引使用情況查詢：sys.dm_db_index_usage_stats 與 operational_stats 完整指南

理解索引如何被使用 (Seek/Scan/Lookup/Update) 是效能調優關鍵。SQL Server 2005+ 提供兩個 DMV：sys.dm_db_index_usage_stats (使用統計) 與 sys.dm_db_index_operational_stats (操作統計)。本文提供查詢腳本、結果解讀與實戰應用場景。

1. sys.dm_db_index_usage_stats：索引訪問模式統計

記錄索引被**讀取** (Seek/Scan/Lookup) 與**維護** (Update) 的累計次數，重啟服務歸零。

SELECT
    OBJECT_NAME(s.[object_id]) AS [TableName],
    i.[name] AS [IndexName],
    s.user_seeks,           -- 索引查找 (B-tree 走索引鍵)
    s.user_scans,           -- 索引掃描 (全索引掃描)
    s.user_lookups,         -- 書籤查找 (非叢集索引回表)
    s.user_updates,         -- 插入/更新/刪除 維護索引次數
    s.last_user_seek,
    s.last_user_scan,
    s.last_user_lookup,
    s.last_user_update
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
    ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
  AND s.database_id = DB_ID()
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC;

結果解讀指南

2. sys.dm_db_index_operational_stats：葉層級操作與鎖/闩統計

提供更細粒度的**葉節點插入/刪除/更新計數**、鎖等待、闩等待、頁面訪問模式，**不因服務重啟歸零** (直到資料庫離線/分離)。

SELECT
    OBJECT_NAME(ops.[object_id]) AS [TableName],
    i.[name] AS [IndexName],
    ops.leaf_insert_count,
    ops.leaf_delete_count,
    ops.leaf_update_count,
    ops.leaf_ghost_count,           -- 幽靈記錄清理計數
    ops.range_scan_count,           -- 範圍掃描計數
    ops.singleton_lookup_count,     -- 單鍵查找計數
    ops.page_latch_wait_count,      -- 頁面闩等待
    ops.page_latch_wait_in_ms,      -- 闩等待總毫秒
    ops.page_io_latch_wait_count,   -- 頁面 I/O 闩等待
    ops.page_io_latch_wait_in_ms    -- I/O 等待總毫秒
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ops
INNER JOIN sys.indexes AS i
    ON i.[object_id] = ops.[object_id] AND i.index_id = ops.index_id
WHERE OBJECTPROPERTY(ops.[object_id], 'IsUserTable') = 1
ORDER BY ops.leaf_insert_count + ops.leaf_update_count + ops.leaf_delete_count DESC;

進階應用：識別熱點頁面爭用

-- 找出頁面闩/ I/O 闩等待最嚴重的索引
SELECT TOP 20
    OBJECT_NAME(ops.[object_id]) AS [Table],
    i.[name] AS [Index],
    ops.page_latch_wait_in_ms,
    ops.page_io_latch_wait_in_ms,
    ops.page_latch_wait_count,
    ops.page_io_latch_wait_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ops
JOIN sys.indexes i ON i.[object_id]=ops.[object_id] AND i.index_id=ops.index_id
WHERE ops.page_latch_wait_in_ms > 0 OR ops.page_io_latch_wait_in_ms > 0
ORDER BY (ops.page_latch_wait_in_ms + ops.page_io_latch_wait_in_ms) DESC;

3. 實戰工作流：索引健康度巡檢

1. 基線建立：業務高峰期運行 1 小時後執行上述查詢，匯出 CSV 存檔。
2. 識別廢棄索引：user_seeks+scans+lookups = 0 AND user_updates > 1000 → 列入刪除候選，發布變更單驗證無報錯後刪除。
3. 識別缺失索引：配合 sys.dm_db_missing_index_* DMV，交叉驗證高 user_scans 表的缺失索引建議。
4. 覆蓋索引優化：高 user_lookups 索引 → 檢查查詢選取清單 → INCLUDE 非鍵欄位消除 Key Lookup。
5. 分區/壓縮評估：高 leaf_insert/delete/update + 高闩等待 → 考慮分區表分散熱點、頁面壓縮減少 I/O。

4. 注意事項

• DMV 數據**服務重啟歸零** (usage_stats) 或**資料庫離線歸零** (operational_stats)，長期趨勢需定期採集存入歷史表。
• 系統表、內部表、記憶體優化表 (Hekaton) 統計可能不準，查詢加 OBJECTPROPERTY(...,'IsUserTable')=1 過濾。
• 唯讀副本 (AlwaysOn Secondary) 不更新 usage_stats，需在主庫收集。
• 索引重建/重組會重置 operational_stats 相關計數器。