SQL Server 備份讀寫機制深度解析：緩衝區、執行緒與參數調校

前言：備份過程的「黑盒」

執行 BACKUP DATABASE 時，SQL Server 內部啟動一套「生產者-消費者」管線：讀取執行緒 從資料檔讀頁 → 寫入共用緩衝區陣列 → 寫入執行緒 從緩衝區取資料寫入備份媒體。理解這套機制，能精準調校 BUFFERCOUNT、MAXTRANSFERSIZE 等進階參數，突破備份效能瓶頸。

一、核心架構：讀/寫執行緒與緩衝區池

• 讀取執行緒：每個邏輯磁碟區 (Volume) 一條。從 .mdf/.ndf 讀取資料頁，寫入緩衝區。
• 寫入執行緒：每個備份目標檔案/裝置一條。從緩衝區取資料寫入磁碟/磁帶。
• 緩衝區池：位於 MemToLeave 區域 (非緩衝集區)，總大小 ≈ BUFFERCOUNT × MAXTRANSFERSIZE + 額外標頭開銷。

二、多磁碟區與多備份檔的並行模型

BACKUP DATABASE AdventureWorks TO
    DISK = 'M:\backups\AW_FULL_01.bak',
    DISK = 'N:\backups\AW_FULL_02.bak'
WITH BUFFERCOUNT = 10, MAXTRANSFERSIZE = 1048576;  -- 1 MB

• 資料檔分佈在 3 個磁碟區 → 3 條讀取執行緒並行讀取。
• 2 個備份檔 → 2 條寫入執行緒並行寫入。
• 緩衝區總計 10 × 1 MB = 10 MB (另加標頭)。

關鍵限制：SQL Server 無法區分邏輯磁碟區與實體磁碟。若 E:/F:/G: 為同一實體磁碟的三個分割區，仍會建立 3 條讀取執行緒，造成磁頭劇烈抖動，反而拖垮吞吐量。務必確認磁碟區對應獨立實體 LUN/RAID 群。

三、關鍵參數：BUFFERCOUNT 與 MAXTRANSFERSIZE

記憶體不足錯誤範例

Server: Msg 701, Level 17, State 1, Line 1
There is insufficient system memory to run this query.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

解決：降低參數、增加實體記憶體、或關閉其他大記憶體消耗程序。

四、追蹤旗標 3605/3213：觀測實際緩衝區配置

DBCC TRACEON(3605, 3213, -1);  -- 全域啟用，輸出至錯誤記錄檔
BACKUP DATABASE ...;
DBCC TRACEOFF(3605, 3213, -1);

錯誤記錄檔輸出範例：

2008-07-14 17:21:20.65 spid51 BufferPoolLimit: 100 MB
2008-07-14 17:21:20.65 spid51 Backup/Restore buffer configuration parameters
2008-07-14 17:21:20.65 spid51 Buffer count: 15
2008-07-14 17:21:20.65 spid51 Max transfer size: 983040
2008-07-14 17:21:20.65 spid51 Total buffer space: 14 MB
2008-07-14 17:21:20.65 spid51 Buffers per read stream: 15
2008-07-14 17:21:20.65 spid51 Buffers per write stream: 5
2008-07-14 17:21:20.65 spid51 Memory Limit: 100 MB

• Buffers per read stream：每條讀取執行緒分配的緩衝區數。
• Buffers per write stream：每條寫入執行緒分配的緩衝區數。
• 總緩衝區 = 讀取執行緒數 × Buffers per read stream (通常等於 BUFFERCOUNT)。

五、實戰調校檢核表

1. 確認實體磁碟拓撲：資料檔、記錄檔、備份目標各自獨立實體磁碟/LUN。
2. 測量基準線：BACKUP ... TO DISK='NUL' WITH COPY_ONLY 取得讀取極限。
3. 調整 MAXTRANSFERSIZE：磁帶依廠商建議；磁碟嘗試 1MB/2MB/4MB。
4. 調整 BUFFERCOUNT：從預設值起調，觀察錯誤記錄檔中的 Total buffer space 與 Memory Limit，確保不觸發 701 錯誤。
5. 多檔案平行備份：TO DISK='file1', DISK='file2'... 增加寫入執行緒。
6. 啟用備份壓縮 (SQL 2008+)：WITH COMPRESSION 以 CPU 換 I/O，通常為淨收益。

結語

備份效能調校的本質是「平衡生產者(讀)與消費者(寫)的速率，並給足緩衝區吸收波動」。透過追蹤旗標觀測實際配置、依實體拓撲規劃平行度、審慎調整 BUFFERCOUNT/MAXTRANSFERSIZE，能將備份吞吐量推向硬體極限，縮短備份視窗、降低 RPO 風險。