SQL Server 海量資料條件刪除高效能策略：避開記錄檔暴漲陷阱

直接對千萬筆資料執行 DELETE WHERE ... 會產生大量記錄檔、鎖定升級、回滾區塊膨脹，導致效能極差甚至填滿磁碟。本文提供三種分級策略，依業務容許度選擇最佳方案。

策略對照表

最佳實務：保留條件資料的「新表置換法」

-- 1. 將需保留資料複製到新表（Full 復原模式下記錄檔較大）
SELECT * INTO Table2 FROM Table1 WHERE Time >= '2006-03-10';

-- 2. 瞬間清空原表（任何復原模式皆不記錄細節）
TRUNCATE TABLE Table1;

-- 3. 重新命名新表為原表名
EXEC sp_rename 'Table2', 'Table1';

關鍵原理解析

• DELETE：逐列記錄、產生回滾資訊、觸發器觸發、鎖定升級為資料表鎖。
• TRUNCATE：DDL 操作、僅記錄頁釋放、不觸發觸發器、重設 Identity、需無外鍵參考。
• SELECT INTO：大量載入、最小記錄（Simple/Bulk-Logged 模式下），Full 模式仍完整記錄。

線上分批刪除範本（不可停機場景）

DECLARE @BatchSize INT = 5000, @RowsAffected INT = 1;
WHILE @RowsAffected > 0
BEGIN
    DELETE TOP (@BatchSize) FROM Table1
    WHERE Time < '2006-03-10';
    SET @RowsAffected = @@ROWCOUNT;
    -- 可選：WAITFOR DELAY '00:00:01' 降低鎖定衝突
    CHECKPOINT; -- Simple 模式下截斷記錄檔
END

操作前檢查清單

1. 確認復原模式：SELECT name, recovery_model_desc FROM sys.databases
2. 預估記錄檔空間：DBCC SQLPERF(LOGSPACE)
3. 檢查外鍵與觸發器：sp_fkeys 'Table1'、sp_helptrigger 'Table1'
4. 備份資料庫或至少備份記錄檔（Full 模式下）。

選對策略，百萬級刪除也能在業務窗口內平穩完成，避免記錄檔暴漲導致服務中斷。