SQL Server 資料庫效能優化全方位指南：設計、應用與作業系統層面

前言：效能優化的關鍵在於設計階段

資料庫是企業資訊核心，選擇高效能產品不等於擁有高效能系統。若設計不當，不僅增加開發維護難度，更嚴重影響執行效能。許多專案在分析、設計、測試階段因資料量小而忽略效能，上線後才發現效能下降，修補成本極高。因此，設計階段是優化的重點。

一、資料庫設計優化

1. 邏輯資料庫正規化

遵循前三正規化：

• 1NF：無重複群組或多值欄位。
• 2NF：非鍵欄位完全依賴主鍵，而非組合主鍵的部分。
• 3NF：非鍵欄位不相互依賴（無傳遞相依）。

正規化產生較少欄位、較多資料表，減少資料冗余與儲存頁數。

2. 物理資料庫實作策略

• 資料型別最小化：索引欄位尤宜使用最小儲存空間的型別（如 smallint 代替 int），加快索引讀取、減少 I/O。
• 資料與索引分離：將資料表與其非叢集索引置於不同實體裝置（透過 SQL Server 區段），善用多磁碟控制器與資料分離技術提升平行 I/O。
• 大型資料表分割：將熱門大表拆分至多個獨立智慧型磁碟控制器的裝置上，多磁頭並行搜尋提升效能。
• 文字/影像欄位獨立儲存：將 text、image 欄位置於專用實體裝置，搭配專用控制器效果最佳。

二、應用系統設計優化

1. 善用索引

• 常用於 JOIN 但非外鍵的欄位建立索引；頻繁 GROUP BY、ORDER BY 的欄位建索引。
• 高選擇性（Distinct 值多）的 WHERE 條件欄位建索引；低選擇性（如「性別」）勿建，否則拖慢更新速度。
• 多欄位排序可建立複合索引。

2. 避免或簡化排序

利用索引自動產生有序輸出，避免大型表重複排序。必要時縮小排序欄位範圍、合併資料表（犧牲部份正規化換取效能）。

3. 消除大型表循序存取

巢狀查詢中循序存取效能殺手。可用 UNION 避免；注意某些 WHERE 子句會強迫優化器採用循序掃描，即使有索引。

4. 避免相關子查詢

外部查詢值改變導致子查詢重複執行，嵌套層次越深效能越差。若不可避免，在子查詢中盡早過濾掉大量資料列。

5. 避免複雜正規表示式

LIKE '98___' 即使有索引仍循序掃描。改為 zipcode > '98000' 可利用索引範圍掃描，大幅提速。

6. 善用暫存表加速查詢

將子集排序後存入暫存表，減少多重排序、簡化優化器工作、降低磁碟 I/O。注意：暫存表不反映主表異動，高頻更新環境需謹慎同步。

三、作業系統層面優化

OS 瓶頸（CPU 過載、記憶體交換、磁碟 I/O）會抵銷資料庫內部調整。使用 Windows 系統監視器 找出瓶頸。

CPU

長期超過 75% 使用率可能為瓶頸。先優化 SQL 陳述式，確認非程式問題後再升級 CPU 或增加核心。

記憶體

SQL Server 可用記憶體是關鍵因素。Buffer Cache Hit Ratio 持續低於 90% 應增加記憶體。足夠記憶體減少實體磁碟讀取，緩解 I/O 壓力。

I/O 子系統

最常見硬體瓶頸。單顆磁碟約 85 IOPS，過載導致排隊、鎖持有時間拉長、執行緒等待。解決方案：增加磁碟數量（RAID、多磁碟陣列）分散 I/O 負載。

結語

影響效能因素眾多，應用各異，無通用銀彈。需在開發維護過程中，針對實際運行情況持續調整優化。