SQL Server 大量 INSERT/DELETE 效能優化：最小化記錄、鎖升級與批次控制

大批量資料搬移與清除需控制記錄檔增長、避免鎖升級、縮短封鎖時間。核心技巧：TABLOCK、TOP(n) 分批、DROPCLEANBUFFERS/FREEPROCCACHE 清理快取模擬真實環境。

大量 INSERT 最佳化

DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE;
SET NOCOUNT ON;
BEGIN TRANSACTION;
INSERT INTO test.dbo.orders WITH(TABLOCK)
SELECT * FROM bak.dbo.Orders
WHERE ordertime BETWEEN '2010-05-01' AND '2010-05-10';
COMMIT;

• WITH(TABLOCK)：啟用最小化記錄（簡易復原模式下）並取得表級鎖，大幅降低記錄檔寫入量與鎖管理成本。
• 單一交易適用於可接受長鎖定的離線/維護窗口操作。

大量 DELETE 最佳化

DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE;
SET NOCOUNT ON;
BEGIN TRANSACTION;
WHILE 1=1 BEGIN
    DELETE TOP(1000) FROM test.dbo.orders WITH(TABLOCK)
    WHERE ordertime BETWEEN '2010-05-01' AND '2010-05-10';
    IF @@ROWCOUNT < 1000 BREAK;
END
COMMIT;

• TOP(1000)：小批次避免鎖升級為表鎖，減少長時間封鎖並發查詢。
• @@ROWCOUNT 判斷結束，無需預先計算總筆數。
• TABLOCK：每批取得表鎖加速刪除，批次間釋放鎖讓並發查詢可進入。

進階選項

• 若資料庫為簡易/大量記錄復原模式，TABLOCK + INSERT 可達最小化記錄。
• 刪除超大表建議配合分區表 SWITCH PARTITION 瞬間切換再 TRUNCATE。
• 操作前後執行 UPDATE STATISTICS 或 ALTER INDEX REBUILD 維護統計資訊。