SQL Server 查詢優化全攻略：從索引設計到架構調優的 50 個實戰技巧

查詢緩慢的十大常見元兇

在投入優化工作前，必須先精準診斷病因。以下是導致 SQL Server 查詢效能低落的十大常見原因：

1. 索引缺失或失效：這是查詢緩慢的頭號殺手，往往源於程式設計階段的疏忽。
2. I/O 吞吐量不足：磁碟讀寫成為系統瓶頸，嚴重拖慢回應速度。
3. 缺乏計算欄位：導致查詢無法利用預先計算的結果進行優化。
4. 記憶體壓力：緩衝區命中率下降，頻繁觸發物理 I/O。
5. 網路延遲：特別影響跨機房或雲端部署的分散式架構。
6. 結果集過大：一次性傳輸海量資料，佔用頻寬與記憶體。
7. 鎖爭用與死鎖：並發控制不當導致阻塞鏈，同樣屬於設計層面缺陷。
8. 資源競爭激烈：可透過 sp_lock、sp_who 觀察活躍使用者與鎖狀態。
9. 冗餘資料輸出：返回不必要的欄位與列，浪費 I/O 與網路資源。
10. SQL 陳述句撰寫不佳：缺乏優化意識的查詢寫法。

架構與硬體層面的優化策略

1. 存儲分離與 I/O 優化

將資料檔、日誌檔、索引檔分別置於不同的 I/O 裝置（如獨立磁碟陣列），可顯著提升讀寫並行度。歷史經驗建議將 Tempdb 放置於 RAID 0（註：SQL Server 2000 後不再支援），資料量越大，I/O 架構設計越關鍵。

2. 資料表分割

採用縱向（欄位拆分）與橫向（資料分區）分割，縮減單表體積。可使用 sp_spaceuse 監控空間使用情況，制定分割策略。

3. 硬體升級

擴充記憶體、增加 CPU 核心、升級存儲設備是最直接但成本最高的手段。Windows 2000 / SQL Server 2000 支援 4-8GB 實體記憶體，虛擬記憶體建議設為實體記憶體 1.5 倍；若啟用全文檢索，建議調整至 3 倍。max server memory 設定為虛擬記憶體的一半（即實體記憶體 1.5 倍）較為適宜。

4. 並行處理權衡

增加 CPU 核心可啟用平行查詢（排序、聯接、掃描、GROUP BY），但平行處理消耗更多記憶體與協調成本。SQL Server 會依負載自動決定平行度。注意：UPDATE/INSERT/DELETE 等修改操作不支援平行執行。

索引與查詢寫法的核心準則

5. 索引設計黃金法則

• 依查詢條件建立索引，優化存取路徑，限制結果集大小。
• 填充因子預設值（0）通常最佳，避免過度分頁。
• 索引鍵越窄越好：優先選擇位元組數小的欄位（如 INT 優於 CHAR(20)）。
• 避免對低基數欄位（如性別）建立單欄索引，選擇率過低導致優化器放棄使用。

6. LIKE 查詢與全文檢索的取捨

• LIKE 'a%' 可用索引；LIKE '%a' 或 LIKE '%a%' 會掃描全表。
• 模糊查詢耗時與欄位總長度成正比，長字串欄位請改用 VARCHAR 而非 CHAR。
• 超長文本欄位建議建立全文索引，雖佔空間但查詢效能指數級提升。

7. 服務分離架構

DB Server 與 Application Server 物理分離；OLTP（線上交易）與 OLAP（分析處理）資料庫分離，避免資源互搶。

8. 分散式分區檢視實現資料庫聯盟

透過水平分區表 + 分散式分區檢視，構建「資料庫伺服器聯盟」。每個成員伺服器僅存儲一部分資料，但應用端透過同名檢視即可透明存取全量資料，實現大型多層 Web 站點的線性擴展。實施步驟：

1. 先完成資料表的水平分區。
2. 在各成員伺服器建立同名的分散式分區檢視。

維護與 T-SQL 進階優化

9. 索引重建與檔案收縮

• 定期執行 DBCC REINDEX、DBCC INDEXDEFRAG 消除碎片。
• 謹慎使用 DBCC SHRINKDB/SHRINKFILE，大型資料庫嚴禁啟用自動增長，應預先規劃空間，避免執行期擴展造成效能抖動。
• 日誌檔可設定自動收縮，但生產環境建議手動控制。

10. 交易控制最佳實踐

• ROLLBACK 回滾所有交易；COMMIT 提交當前交易。
• 動態 SQL 中避免直接寫入交易控制，應包裝於外層：
  BEGIN TRAN EXEC(@sql) COMMIT TRAN
• 或將動態 SQL 封裝為預存程序/函數，由外部管理交易邊界。

11. WHERE 子句的防禦性編程

查詢務必加上 WHERE 條件限制回傳列數，避免全表掃描。大表掃描期間會鎖定資源，阻塞其他聯接存取，後果嚴重。同時避免 SELECT *，僅選取必要欄位，減輕 I/O 與網路負擔。

12. 查詢優化器內部流程解析

1. 詞法/語法檢查
2. 提交查詢優化器
3. 代數優化與存取路徑優化
4. 預編譯模組生成查詢計劃
5. 排程執行
6. 返回結果

資料儲存採 8KB 頁面（8060 字節可用），8 頁為 1 區間，採 B-Tree 結構組織。

13. 註釋零負擔

SQL 註釋（-- 或 /* */）僅在編譯階段解析，對執行效能零影響，放心使用以提升可維護性。