SQL Server 高頻大量資料更新優化策略：分批處理、分區表與鎖升級控制

問題痛點

• 每 2-3 分鐘更新數張表，單表幾十萬至百萬筆。
• UPDATE 觸發鎖升級 (Row → Page → Table)，阻塞並發查詢/寫入。
• 交易記錄暴增，恢復模型為 Full 時記錄檔暴長。

核心優化方案組合

1. 分批次更新 — 避免大交易長鎖定

DECLARE @BatchSize INT = 5000;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    BEGIN TRANSACTION;
    
    UPDATE TOP (@BatchSize) TargetTable
    SET Col1 = Source.Col1,
        Col2 = Source.Col2,
        UpdatedAt = GETDATE()
    FROM TargetTable
    INNER JOIN SourceTable ON TargetTable.ID = SourceTable.ID
    WHERE TargetTable.Col1 <> Source.Col1 
       OR TargetTable.Col2 <> Source.Col2; -- 僅更新真正變更的行
    
    SET @RowsAffected = @@ROWCOUNT;
    COMMIT TRANSACTION;
    
    -- 關鍵：釋放鎖、讓出 CPU、記錄檔截斷 (Simple 模式)
    WAITFOR DELAY '00:00:00.050'; -- 50ms 間隔
END

2. 索引優化 — 精準定位減少掃描範圍

• 確保 WHERE 與 JOIN 條件欄位有覆蓋索引。
• 避免 UPDATE 修改叢集索引鍵值 (Key 變更 = Delete + Insert，雙倍日誌)。

3. 鎖粒度控制

-- 強制列鎖 (避免頁/表鎖升級)
UPDATE TargetTable WITH (ROWLOCK)
SET ...

-- 或資料庫層級關閉鎖升級 (SQL 2019+)
ALTER DATABASE SCOPED CONFIGURATION SET LOCK_ESCALATION = DISABLE;

4. 分區表 — 邏輯隔離熱資料

• 依時間/區域分區，僅更新最新分區，歷史分區設為 READ_ONLY。
• 分區切換 可秒級載入新資料，無鎖競爭。

5. 替代架構：CDC / 變更追蹤 + 非同步同步

• 啟用 Change Data Capture (CDC) 捕捉異動。
• 由獨立 ETL 程序/Service Broker 非同步套用至目標表，核心交易系統零阻塞。

6. 硬體/設定層面

• TempDB 多檔案：減少 PFS/GAM 競爭。
• 延遲持久性：ALTER DATABASE SET DELAYED_DURABILITY = FORCED (允許少量資料遺失換高吞吐)。
• 記憶體優化表：熱點表遷移至 In-Memory OLTP (Hekaton)，無鎖無閂。

執行優先順序建議

1. 分批次 + 索引優化 (立竿見影，無架構變更)。
2. 調整鎖升級 / ROWLOCK (參數調整)。
3. CDC 非同步同步 (架構重構，徹底解耦)。
4. 分區表 / In-Memory OLTP (企業版功能，長期演進)。