[SQL] 索引使用統計資訊

建立 Index 來提高效能後，也要定期觀察該 Index 被使用的情況，可以利用 sys.dm_db_index_usage_stats DMV 來查詢 Index 使用統計資訊

sys.dm_db_index_usage_stats DMV 語法

SELECT 
  s.name AS SchemaName ,
  t.Name AS TableName ,
  ix.Name AS IndexName ,
  ix.type_desc ,
  us.user_seeks ,
  us.user_scans ,
  us.user_lookups ,
  us.user_updates  
FROM sys.tables AS t 
  JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
  JOIN sys.indexes AS ix ON t.[object_id] = ix.[object_id]
  JOIN sys.dm_db_index_usage_stats AS us ON ix.[object_id] = us.[object_id]
                                           AND ix.index_id = us.index_id    
WHERE t.type = 'U' -- 使用者自訂 Table

sys.dm_db_index_usage_stats 重點欄位

1. user_seeks：由使用者查詢所進行的搜尋數
2. user_scans：由使用者查詢所進行的掃描數
3. user_lookups：由使用者查詢所進行的書籤查閱數
4. user_updates：由使用者查詢所進行的更新數

上述四個欄位觀察重點：

1. user_seeks 為 0：該索引完全沒有作用，移除
2. user_seeks 數字越大，user_scans 數字越小：該索引有充分被使用到
3. user_seeks 數字越小，user_scans 數字越大：考慮移除
4. user_seeks 遠小於 user_updates：持續更新索引，但卻沒甚麼使用到，考慮移除
5. user_lookups 數字越大：確認是否需要把該索引改為包含索引

會利用此方式來觀察，索引存在是否合理

延伸閱讀
• [SQL] 小型索引
• [SQL] 具有內含資料行的索引

參考資料
• 論壇問題討論
• 如何找出 SQL Server 2008 資料庫裡是否建立了多餘的索引
• 認識 sys.dm_db_index_usage_stats 目錄檢視，查詢與索引作業相關的計數器資料
• [SQL]找出DB中沒有使用或成本高的索引資訊
• How to get index usage information in SQL Server
• 網路討論 - 說明 Clustered Index 的 user_lookup 產生原因