SQL Server 索引碎片整理：偵測、重建與線上操作最佳實務

長期的 INSERT/UPDATE/DELETE 會產生索引碎片，導致查詢效能下降。解決之道在於 重建 或 重組 索引。本文提供完整偵測腳本與操作指引。

一、偵測碎片程度

DECLARE @db_id INT = DB_ID(N'YourDB');
DECLARE @obj_id INT = OBJECT_ID(N'YourSchema.YourTable');

IF @db_id IS NULL PRINT N'Invalid database';
ELSE IF @obj_id IS NULL PRINT N'Invalid object';
ELSE
BEGIN
    SELECT
        OBJECT_NAME(ips.object_id) AS TableName,
        i.name AS IndexName,
        ips.index_type_desc,
        ips.avg_fragmentation_in_percent,
        ips.page_count
    FROM sys.dm_db_index_physical_stats(@db_id, @obj_id, NULL, NULL, 'LIMITED') ips
    JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE ips.avg_fragmentation_in_percent > 10  -- 只看有碎片的
    ORDER BY ips.avg_fragmentation_in_percent DESC;
END;

碎片門檻參考：

• < 10%：健康，無需處理
• 10% ~ 30%：重組 (ALTER INDEX ... REORGANIZE)，線上、低鎖定、單執行緒
• > 30%：重建 (ALTER INDEX ... REBUILD)，可指定 WITH (ONLINE = ON) 企業版支援線上重建

二、重建 vs 重組 指令

三、自動化維護建議

1. 建立 sp_IndexMaintenance 預存程序，接受 @FragmentationLow、@FragmentationHigh 參數
2. 搭配 SQL Agent 排程，每日/每週非尖峰時段執行
3. 啟用 ONLINE = ON（企業版）避免鎖定業務
4. 重建後同步更新統計資料：UPDATE STATISTICS Schema.Table 或重建時加 WITH (STATISTICS_NORECOMPUTE = OFF)

注意： REBUILD 需足夠磁碟空間（約 1.2 倍索引大小），REORGANIZE 則不需要額外空間。