SQL Server 資料庫查詢緩慢？十大常見原因與優化實戰指南

SQL Server 查詢效能不佳的成因眾多，以下整理十大常見原因與對應優化策略：

十大常見效能瓶頸

1. 缺少索引或未用到索引（最常見，屬程式設計缺失）
2. I/O 吞吐量不足，形成瓶頸
3. 未建立計算欄位導致查詢未優化
4. 記憶體不足
5. 網路速度過慢
6. 查詢回傳資料量過大（可採分批查詢等方式降低資料量）
7. 鎖定或死結（常見，屬程式設計缺失）
8. 資源競爭（可用 sp_lock、sp_who、活動使用者查看）
9. 回傳不必要的資料列與欄位
10. 查詢語句撰寫不佳，未經優化

優化查詢的實務方法

1. 檔案配置分離

將資料、記錄檔、索引放置於不同 I/O 裝置，提升讀取速度。資料量越大，I/O 優化越關鍵。

2. 資料表縱向/橫向分割

利用 sp_spaceuse 分析，將大表拆分縮小尺寸。

3. 硬體升級

擴充記憶體（Win2000/SQL2000 支援 4-8GB）、提升網速、增加 CPU 核心數。注意：平行處理比序列處理更耗資源（如記憶體），SQL Server 會依負載自動決定平行度。複雜、高 CPU 耗用的查詢適合平行處理，但 UPDATE/INSERT/DELETE 目前無法平行化。

4. 索引優化

• 依查詢條件建立索引，優化存取路徑，限制結果集大小
• 填充因子建議使用預設值 0
• 索引越小越好，優先對位元組數小的欄位建索引
• 避免對基數極低的欄位（如性別）建單一索引

5. 記憶體與虛擬記憶體設定

• 虛擬記憶體建議設為實體記憶體 1.5 倍
• 若啟用全文檢索，建議設為 3 倍
• max server memory 設為虛擬記憶體一半（即實體記憶體 1.5 倍）

6. LIKE 查詢優化

• LIKE 'a%' 可使用索引
• LIKE '%a'、LIKE '%a%' 無法使用索引
• 長字串欄位建議使用 VARCHAR 而非 CHAR，並考慮全文索引

7. 架構分離

DB Server 與 Application Server 分離；OLTP 與 OLAP 分離。

8. 分散式分割檢視表

實現資料庫伺服器聯盟，支援大型多層 Web 站點擴展。需先水平分割資料表，再於各成員伺服器建立同名分散式分割檢視表。

9. 索引維護與資料庫收縮

• 定期重建索引：DBCC REINDEX、DBCC INDEXDEFRAG
• 收縮資料/記錄檔：DBCC SHRINKDB、DBCC SHRINKFILE
• 設定自動收縮記錄檔
• 大型資料庫勿設定自動成長，會降低效能

10. T-SQL 撰寫技巧

• 盡量避免使用游標，改用暫存表、表值變數、子查詢、CASE 陳述式等集合式操作
• 游標並發選項：READ_ONLY、OPTIMISTIC WITH VALUES 等
• COMMIT 提交目前交易，ROLLBACK 回滾所有交易；動態 SQL 中建議於外層控制交易
• WHERE 子句限制回傳列數，避免全表掃描造成鎖定與 I/O 浪費
• 註解不影響執行效能

掌握上述原則，並配合實際執行計畫分析，即能大幅提升 SQL Server 查詢效能。