SQL Server 億級大表操作效能優化實戰：分區、磁碟 I/O 與日誌控制

痛點現象

• 兩億筆資料 GROUP BY 導致 CPU、記憶體、磁碟全滿載
• SELECT COUNT(*) 耗時 1 分鐘，記憶體暴漲 5GB
• 大量磁碟 I/O 導致整台伺服器回應變慢

根因分析

1. 單表過大：全表掃描、排序、雜湊運算資源消耗指數級增長
2. 磁碟爭用：資料庫與系統盤共用磁碟陣列，I/O 干擾作業系統
3. 自動成長設定不當：每次 1MB 成長，頻繁擴展產生碎片與等待
4. 刪除操作產生大量日誌：日誌檔縮小後重新成長，加劇磁碟負載

解決方案

1. 資料分區：將大表拆小

• 按業務維度（省份、時間、租戶）水平分表，單表控制在千萬級
• 應用層路由或分區視圖合併查詢結果
• 配合分區索引消除跨分區掃描

2. 磁碟隔離：專用存儲陣列

• 資料檔、日誌檔、tempdb 分置不同物理磁碟/RAID 組
• 避免與 OS、頁面檔爭用 I/O 頻寬
• 考慮 NVMe SSD 做熱數據，HDD 做冷存檔

3. 檔案成長策略優化

• 預先配置足夠空間，關閉自動成長或設定固定大幅成長（如 512MB/1GB）
• 啟用瞬間檔案初始化（Windows 需授權 Perform volume maintenance tasks）
• 監控剩餘空間，設定告警閾值（如 < 20% 發送郵件）

4. 日誌管理

• 定期 BACKUP LOG 截斷非作用中 VLF
• 簡單復原模式下定期 CHECKPOINT
• 大量刪除改用分批 DELETE TOP (N) + WAITFOR DELAY 避免日誌爆增
• 考慮 TRUNCATE TABLE 或分區切換替代大量刪除

架構層面建議

• 讀寫分離：主庫寫入、從庫報表查詢
• 引入快取層（Redis）吸收高頻讀取
• 冷熱資料分離：歷史資料歸檔至廉價存儲或 ClickHouse/Apache Doris 等 OLAP 引擎