SQL Server 資料庫備份加速全攻略：讀寫吞吐量瓶頸分析與優化實戰

前言：備份速度由什麼決定？

完整備份的速度取決於兩大核心管道的吞吐量：

1. 讀取吞吐量：從資料檔讀取資料頁的速度（受限於磁碟讀取效能、檔案分佈、碎片程度）。
2. 寫入吞吐量：將備份資料寫入目標媒體的速度（受限於備份目標磁碟/磁帶效能、檔案數量、壓縮）。

整體備份速度 = min(讀取吞吐量, 寫入吞吐量)。任一端成為瓶頸，另一端再快也無濟於事。

一、讀取吞吐量：測量與優化

1. 如何測量讀取吞吐量

• 方法 A：完整備份 + 效能監視器
  執行完整備份，同時用 perfmon 監控資料檔所在磁碟的 Read Bytes/sec 計數器。
  關鍵：備份檔必須放在「不同實體磁碟」，否則讀寫干擾導致測量不準。
• 方法 B：備份到 NUL 裝置 (SQL 2005+)
  BACKUP DATABASE AdventureWorks TO DISK = 'NUL' WITH COPY_ONLY
COPY_ONLY 避免破壞差異備份基底；NUL 丟棄寫入，純測讀取極限。
  警告：SQL 2000 無 COPY_ONLY，備份到 NUL 會被視為有效備份，後續差異/記錄備份鏈會斷裂，務必事後補全完整備份。

2. 提升讀取吞吐量的三大策略

二、寫入吞吐量：瓶頸識別與突破

1. 實測案例：同磁碟 vs. 不同磁碟

-- 備份檔與資料檔在同一磁碟
BACKUP DATABASE AdventureWorks TO DISK = 'M:\backups\AW.bak'
-- 結果：7529 pages in 3.300 seconds (18.688 MB/sec)  ← 寫入瓶頸

-- 備份檔放在不同實體磁碟
BACKUP DATABASE AdventureWorks TO DISK = 'N:\backups\AW.bak'
-- 結果：7529 pages in 1.421 seconds (43.399 MB/sec)  ← 讀寫平衡，接近讀取極限 46 MB/sec

2. 提升寫入吞吐量的四大手段

1. 備份檔放在獨立實體磁碟：避免與資料檔爭奪磁碟頻寬（最立竿見影）。
2. 多檔案平行寫入：
   BACKUP DATABASE ... TO DISK='file1.bak', DISK='file2.bak', DISK='file3.bak'
   SQL Server 會建立多條寫入執行緒，若磁碟控制器支援多佇列，效能近線性擴展。
3. 使用更快的備份儲存：專用備份磁碟陣列、磁帶庫、虛擬磁帶庫 (VTL)、雲端備份閘道。
4. 備份壓縮 (SQL 2008+)：
   WITH COMPRESSION 減少寫入資料量，以 CPU 換 I/O。
   需評估 CPU 預留容量，避免擠壓工作負載。

三、系統層面的設計原則：從建庫開始佈局

以下原則同時適用於備份效能與日常查詢效能，建議在資料庫初始規劃階段即納入考量。

• 磁碟速度：預算允許下採用最快磁碟子系統 (NVMe > SSD > SAS > SATA)。
• 多檔案群組：建立多個檔案群組，將核心表、索引、大型物件 (LOB) 分散不同實體磁碟，啟用並行 I/O。
• 實體磁碟隔離：邏輯分割區 ≠ 實體隔離。確保檔案群組對應不同實體 LUN/RAID 群。
• 預先配置大小與成長策略：避免頻繁自動成長產生碎片；初始大小 = 預估峰值 80%，成長量固定值 (如 512MB) 而非百分比。
• 交易記錄獨立磁碟：記錄檔 I/O 為循序寫入，與資料檔隨機讀寫隔離，保護記錄備份速度與復原鏈完整性。
• 備份目標獨立磁碟：備份寫入不干擾生產 I/O，且具備足夠頻寬應對並行多檔案備份。

四、從歷史紀錄獲取備份速度基準線

SELECT
    database_name,
    backup_start_date,
    CAST(CAST((backup_size / (DATEDIFF(ss, backup_start_date, backup_finish_date))) / (1024.0 * 1024.0) AS NUMERIC(8,3)) AS VARCHAR(16)) + ' MB/sec' AS speed
FROM msdb..backupset
ORDER BY database_name, backup_start_date;

backup_size 已扣除已刪除資料頁 (SQL 2005+)，反映實際寫入量。

結語

備份加速的本質是「找出瓶頸端、針對性擴充頻寬」。先測讀取極限，再確保寫入端不拖後腿；長遠來看，磁碟子系統架構與檔案佈局才是根本。建立基準線、定期複測、納入容量規劃，才能在資料成長時保持備份視窗可控。