SQL Server 億級大表排序與 Join 效能優化實戰

問題現象

針對擁有兩億筆記錄的資料表執行 GROUP BY 或大型 JOIN，導致 CPU、記憶體與磁碟 I/O 全面飆升，甚至造成整台伺服器回應變慢。單純執行 SELECT COUNT(*) 即耗時 1 分鐘、記憶體暴增 5 GB、磁碟子系統負載極高。

根因分析

1. 查詢規模過大：單一查詢掃描海量資料，產生大量中間結果集，超出記憶體容量導致大量磁碟溢寫。
2. I/O 競爭：資料庫與作業系統共用磁碟陣列，大量查詢 I/O 排擠系統作業，造成整體效能下降。
3. 自動成長設定不當：資料檔案以 1 MB 為單位自動成長，空間不足時頻繁觸發成長操作，產生額外 I/O 開銷。
4. 交易記錄膨脹：大量 DELETE 產生海量記錄，搭配記錄檔自動成長進一步加劇磁碟壓力。

解決方案

1. 資料分區與批次處理

依業務維度（如地區、時間）將大表拆分為 30 個以上的小表或分區表。查詢時針對單一分區操作，最後再合併結果，避免全表掃描。

2. 磁碟資源隔離

將資料庫檔案（資料、記錄、tempdb）分置於獨立磁碟陣列，避免與作業系統爭奪 I/O 頻寬。

3. 主動空間管理

• 預先配置足夠資料檔案空間，設定合理成長增量（建議固定大小如 512 MB 或 1 GB）。
• 建立空間監控告警，檔案成長時即時通知 DBA 擴容。
• 定期清理無效臨時資料，維持充足可用空間。

4. 記錄檔維護

定期執行記錄截斷或備份，控制記錄檔大小；大量刪除操作改用分批提交或 TRUNCATE TABLE（符合條件時）減少記錄產生。

最佳實務總結

大表優化核心在於「化整為零」與「資源隔離」。結合分區表、檔案群組規劃、主動容量管理與適當的索引策略，才能在億級資料量下維持穩定效能。