前言
在 Microsoft SQL Server 2000 中，內建多種優化工具可有效提升資料庫效能。雖然「優化」常被視為高階技能，但掌握核心原則後，任何人都能成為優化高手。本文將從十二個面向深入探討 SQL Server 2000 效能優化，助您獲得最佳效能收益。
核心優化操作：備份、大量複製與 DBCC
獲得最大效能提升的三大關鍵操作包括：
- 備份與還原作業
- 大量將資料複製至資料表
- 執行資料庫主控台命令 (DBCC) 作業
一般情況下無需特別優化這些操作，但在效能至關重要的場景中，掌握以下技巧將事半功倍。
優化備份與還原效能
平行備份設備提升吞吐量
使用多個備份裝置可實現平行寫入，同樣還原時也能從多裝置平行讀取。備份裝置速度常是吞吐量瓶頸，增加裝置數量可成比例提升吞吐量。
混合備份策略縮短恢復時間
結合完整備份、差異備份與交易記錄備份，可將故障恢復時間降至最低。差異備份減少需應用的交易記錄量，通常比建立完整備份更快。
記錄與最小記錄大量複製
針對大量複製操作，可選擇記錄或最小記錄模式，平衡效能與復原需求。
深度解析：資料庫、差異與檔案備份效能
備份雙步驟機制
建立資料庫備份包含兩步：
- 將資料從資料庫檔案複製至備份裝置
- 將交易記錄中用於前滾至一致狀態的部分複製至同一備份裝置
差異備份同樣包含這兩步，但僅複製已變更的資料（雖需讀取所有頁面判斷變更）。檔案備份僅需第一步。
平行讀寫架構
資料庫檔案依磁碟裝置排序，每個裝置指派讀取器執行緒；每個備份裝置指派寫入器執行緒。將資料庫檔案分散於更多邏輯磁碟機可增加平行讀取；使用更多備份裝置可增加平行寫入。
瓶頸識別與解決
- 讀取吞吐量 > 備份吞吐量：瓶頸在備份裝置，增加備份裝置（必要時加 SCSI 控制器）可提升效能
- 備份吞吐量 > 讀取吞吐量：應增加資料庫檔案或在 RAID 陣列加入更多磁碟以提升讀取能力
交易記錄備份效能優化
交易記錄備份僅單步驟：將未備份的記錄部分複製至備份裝置。雖可有多個記錄檔，但邏輯上為單一循序讀取流。每個備份裝置指派讀寫執行緒，增加裝置可線性提升效能，直到受限於記錄檔所在磁碟最大容量。
還原作業效能深度剖析
四階段還原流程
- 建立資料庫與交易記錄檔（若不存在）
- 從備份裝置複製資料至資料庫檔案
- 從交易記錄檔複製交易記錄
- 前滾交易記錄，必要時重新啟動恢復
關鍵效能因素
- 檔案建立與初始化：使用獨立工作執行緒平行建立，均勻分散檔案於邏輯磁碟機可獲最佳效能
- 資料複製階段：效能受限於備份裝置傳送能力或資料庫檔案接收能力，隨備份裝置增加線性提升至極限
- 前滾交易記錄：效能固定，僅能透過更快硬體提升
磁帶與磁碟備份裝置優化
磁帶裝置四大關鍵變數
- 軟體資料區塊大小（由 SQL Server 自動計算最佳值，勿手動更改）
- 共用 SCSI 總線的磁帶裝置數量
- 磁帶裝置類型
- 高速磁帶機建議使用專用 SCSI 總線，本地傳輸率超過總線 50% 時必須專用
重要警告：切勿將磁帶機與磁碟或 CD-ROM 置於同一 SCSI 總線，錯誤處理機制不相容。
磁碟備份與 RAID 考量
- RAID 5 寫入效能較低，約等同單顆磁碟速度（因維護奇偶校驗）
- 高度條帶化備份裝置時，在同一條帶集放置多個邏輯備份裝置可能提升效能
- 一般建議每個備份裝置獨立放置於單一磁碟裝置
- SCSI 總線磁碟數量有限，Ultra-wide/Ultra-2 總線支援更多磁碟，需精心配置硬體
資料壓縮與傳輸量優化
硬體壓縮效益
現代磁帶機內建硬體壓縮，典型壓縮比 1.2:1 至 2:1，可顯著提升有效傳輸率。主要包含已壓縮影像的資料庫無法進一步壓縮。SQL Server 預設支援硬體壓縮，極少數情況下（如資料已完全壓縮）可用追蹤旗標 3205 關閉以避免浪費 CPU。
備份資料量特性
資料庫備份僅擷取含實際資料的部分，不備份未使用空間，加快備份速度。資料庫自動成長設定下，保留內部空間不影響備份吞吐量與總耗時。
DBCC 效能優化策略
DBCC 大量佔用 CPU 與磁碟，需讀取每個資料頁。系統忙碌時可用記憶體減少，SQL Server 被迫將資料頁送至 tempdb 假脫機處理，拖垮效能。
核心建議
- 為 DBCC 騰出更多記憶體，增加資料頁快取
- 將 tempdb 置於與使用者資料庫分離的快速磁碟（如 RAID 陣列）
- 執行 DBCC CHECKDB 時會自動執行 CHECKTABLE 與 CHECKALLOC，無需單獨執行
大量複製效能優化選項
使用 bcp 工具或 BULK INSERT 陳述式時，可透過以下選項極大提升速度：
- 選擇有記錄或無記錄大量複製
- 使用 bcp 平行資料載入
- 控制鎖定行為
- 使用批次處理
- 預先排序資料檔案
最佳實務：若可行，優先使用 BULK INSERT 陳述式，其效能優於 bcp 工具。
決定優化策略的兩大因素
- 目標資料表現有資料量與待複製資料量的比例
- 資料表上索引的數量與類型
另需考慮是從單一用戶端還是多用戶端平行複製資料。
單一用戶端載入空資料表最佳實務
（後續內容將詳細說明具體操作步驟與參數調整建議）
