SQL Server 索引使用情況全景掃描：讀寫比、空間佔用與最後存取時間

整合 sys.indexes、sys.dm_db_index_usage_stats、sys.dm_db_partition_stats，產出單一查詢掌握所有索引的讀取/寫入次數、最後使用時間、空間佔用與讀寫差異，快速識別未使用、低效、過大索引。

完整查詢腳本

SELECT 
    sch.name + '.' + t.name AS [Table Name],
    i.name AS [Index Name],
    i.type_desc,
    ISNULL(user_updates, 0) AS [Total Writes],
    ISNULL(user_seeks + user_scans + user_lookups, 0) AS [Total Reads],
    s.last_user_seek,
    s.last_user_scan,
    s.last_user_lookup,
    ISNULL(user_updates, 0) - ISNULL(user_seeks + user_scans + user_lookups, 0) AS [Difference],
    p.reserved_page_count * 8.0 / 1024 AS SpaceInMB
FROM sys.indexes AS i WITH (NOLOCK)
LEFT JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK) 
    ON s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id = DB_ID()
JOIN sys.tables AS t WITH (NOLOCK) ON i.object_id = t.object_id
JOIN sys.schemas AS sch WITH (NOLOCK) ON t.schema_id = sch.schema_id
LEFT JOIN sys.dm_db_partition_stats AS p WITH (NOLOCK) 
    ON i.index_id = p.index_id AND i.object_id = p.object_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY [Table Name], [Index Name];

輸出欄位決策指南

進階篩選選項（註解區可啟用）

-- 僅顯示從未使用的索引
-- AND ISNULL(user_updates,0) - ISNULL(user_seeks+user_scans+user_lookups,0) > 0

-- 僅非主鍵索引
-- AND i.index_id > 1 AND i.is_primary_key = 0 AND i.is_unique_constraint = 0

重要提醒

• dm_db_index_usage_stats **服務重啟歸零**，長期分析需定期持久化至歷史表。
• 系統啟動後立即查詢會誤判「未使用」，需跑足夠代表性負載週期。
• 唯一約束/主鍵索引雖讀取少，但承擔資料完整性職責，**不可輕易移除**。