SQL Server 索引碎片率檢測與內外部碎片判斷標準

一鍵掌握索引碎片健康狀態

索引碎片是導致查詢效能下降的隱形殺手。本腳本針對 外部碎片率 > 10% 與 內部頁面填滿度 < 75% 兩大指標，自動標記「碎片率高」或「正常」，並列出具體資料表、索引名稱與數值，供 DBA 快速決定是否需重建或重組索引。

判斷邏輯

查詢腳本

SELECT 
    OBJECT_NAME(dt.object_id) AS 表名,
    si.name AS 索引名,
    外部碎片率 = CASE 
        WHEN dt.avg_fragmentation_in_percent > 10 THEN N'碎片率高' 
        ELSE N'正常' 
    END,
    內部碎片率 = CASE 
        WHEN dt.avg_page_space_used_in_percent < 75 THEN N'碎片率高' 
        ELSE N'正常' 
    END
FROM (
    SELECT 
        object_id,
        index_id,
        avg_fragmentation_in_percent,
        avg_page_space_used_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID('jinriairv2'), NULL, NULL, NULL, 'DETAILED')
    WHERE index_id <> 0
) AS dt
INNER JOIN sys.indexes si 
    ON si.object_id = dt.object_id 
   AND si.index_id = dt.index_id
   AND dt.avg_fragmentation_in_percent > 10
   AND dt.avg_page_space_used_in_percent < 75
ORDER BY dt.avg_fragmentation_in_percent DESC;

關鍵參數說明

• DB_ID('jinriairv2')：請替換為實際資料庫名稱或使用 DB_ID() 取得目前資料庫。
• 'DETAILED' 掃描模式最準確但耗時最長，大型資料庫建議改用 'SAMPLED' 或 'LIMITED'。
• 結果已在 JOIN 條件中過濾，僅回傳「雙高」碎片的索引，減少雜訊。

後續動作建議

1. 5% ~ 30%：執行 ALTER INDEX ... REORGANIZE（線上、輕量）。
2. > 30%：執行 ALTER INDEX ... REBUILD（可加 ONLINE = ON 企業版）。
3. 內部碎片率高：考慮調整 FILLFACTOR 重建索引，預留頁面空間減少分頁分裂。
4. 納入定期維護計畫（如每週執行），搭配 sys.dm_db_index_usage_stats 避免維護未使用的索引。