常見效能瓶頸分類
- I/O 吞吐不足（磁碟、RAID、檔案群配置）
- 缺乏適當索引 / 統計資訊過期
- 記憶體壓力（Buffer Pool、Plan Cache、工作區記憶體）
- 網路頻寬/延遲
- 單次回傳過大結果集
- 鎖定/死鎖（阻塞鏈、升級為表鎖）
- 不必要的掃描（缺 WHERE、隱性轉換、SARGable 違反）
- 劣質查詢計畫（參數嗅探、統計資訊不準）
基礎架構層優化（1~11）
- 分離檔案群：資料、記錄、索引、Tempdb 分置不同實體磁碟/RAID 組，平衡 I/O。
- 垂直/水平分割大表：依存取熱度拆欄位（垂直）或依範圍/雜湊拆資料列（水平/分區檢視/分區表）。
- 硬體升級：更快 CPU、更多核心、NVMe SSD、大記憶體。
- 索引策略：依查詢條件建立覆蓋索引、填滿因子預留空間、避免低選擇性欄位（如性別）建單欄索引。
- 網路優化：啟用壓縮、減少往返、使用連線共用。
- 記憶體配置：
max server memory 留 1~4 GB 給 OS，虛擬記憶體設為實體 1.5~3 倍，啟用 Lock Pages in Memory（Enterprise）。
- CPU 並行度：
MAXDOP 依核心數設定（≤8 核 = 核心數，>8 核 = 8），cost threshold for parallelism 提高至 50 避免小查詢並行。
- LIKE 模糊查詢：
LIKE 'abc%' 可用索引，LIKE '%abc' / LIKE '%abc%' 全掃描 → 改用全文檢索或 n-gram 表。
- OLTP/OLAP 分離：讀寫分離、唯讀副本、資料倉庫/Analysis Services。
- 分散式分區檢視：跨伺服器水平分割，形成伺服器聯盟，透明擴展。
- 維護作業：定期
DBCC INDEXDEFRAG/REBUILD、UPDATE STATISTICS、DBCC SHRINKFILE（謹慎）、關閉 Auto Shrink、謹慎用 Auto Grow（預先配置足夠空間）。
T-SQL 撰寫層優化（12~50）
- 交易範圍最小化：動態 SQL 包在
BEGIN TRAN ... COMMIT 外層，或改用 SP/函數。
- WHERE 子句限制回傳列：避免
SELECT *、避免無條件掃描導致表鎖。
- 註解不影響執行：但建議保持簡潔。
- 少用游標：改用集合式操作、Table-valued Parameter、CTE、
CASE 表達式、暫存表/表變數。
- Profiler / Extended Events 追蹤：找出高耗時/高 I/O/高 CPU 陳述式，配合 Database Engine Tuning Advisor。
- UNION ALL 優於 UNION：去重排序成本高，無重複風險時用 ALL。
- 避免不必要的 DISTINCT/ORDER BY：移至應用層處理。
- 限制結果集：
TOP (N)、SET ROWCOUNT、QUERY_GOVERNOR_COST_LIMIT。
- SARGable 條件寫法：
✅ WHERE col = 'value'、WHERE col > @dt、WHERE col LIKE 'prefix%'
❌ WHERE YEAR(col) = 2023、WHERE ISNULL(col,'') = ''、WHERE col + 1 = 10
→ 改用計算欄位 + 索引、或改寫為範圍查詢。
- 查看執行計畫：關注 Table Scan、Key Lookup、Sort、Hash Match、Parallelism、Row Estimate vs Actual 差異。
- 強制索引提示：
WITH (INDEX(idx_name)) 僅作最後手段，優先修正統計資訊/查詢寫法。
- 預先計算彙總表：儀表板/報表高頻查詢，排程刷新彙總表。
- MIN/MAX 利用索引：極值查詢走索引最佳化。
- 業務邏輯下沉：Default → Rule/Trigger → Constraint → Stored Procedure，越靠近儲存引擎越高效。
- 大二進位欄位用參數化 SP：避免應用層 Base64/HEX 雙向轉換浪費 CPU/網路。
- BETWEEN 優於 IN（連續範圍）：優化器更易轉為範圍掃描。
- 暫存表索引視情況建立：大暫存表 + 複雜查詢才值得，小表反而拖慢。
- 避免無謂交易：唯讀報表不需
BEGIN TRAN。
- OR 條件拆分為 UNION ALL：各分支可分別利用索引，再合併。
- 少用 View 巢狀：每層 View 增加解析開銷，改用 Inline TVF 或 SP。
- IN 清單最常見值置前：短路求值減少比較次數。
- SELECT INTO 鎖系統表：改用
CREATE TABLE #t ... INSERT INTO #t SELECT ...，避免長時間阻塞 sysobjects。
- HAVING 放最後：
WHERE → GROUP BY → HAVING 順序剔除資料最省資源。
- 批次更新優於逐筆：單陳述式更新多列，減少鎖升級與日誌刷新。
- 表變數優於暫存表（小資料量）：無統計資訊、無重編譯、記憶體常駐，但大資料量缺乏統計資訊易產生劣質計畫。
- 計算欄位可建索引：需
DETERMINISTIC、PRECISE、ANSI_NULLS ON 等 SET 選項正確。
- 邏輯下推到伺服器：SP/UDF 封裝複雜運算，減少網路往返。
- 避免重複呼叫標量函數：結果存變數重用。
- EXISTS 優於 COUNT(*)：
IF EXISTS (SELECT 1 ...) 短路，COUNT 需掃描全表。
- 執行緒配置：記憶體充足時
max worker threads = max connections + 5，否則啟用 Thread Pooling。
- 隱性轉換寫常數：
WHERE float_col > 3000.0 而非 3000，避免執行期 CONVERT。
- JOIN 條件順序：優化器通常能最佳化，但複雜查詢可用
FORCE ORDER 或重寫順序輔助。
- 動態 SQL 參數化：
sp_executesql @sql, N'@p int', @p 重用計畫，防注入。
- 死鎖預防：全系統統一資源存取順序（如先表 A 後表 B），縮短交易、降低隔離等級、使用 Row Versioning (RCSI/SNAPSHOT)。
- Performance Monitor 關鍵計數器：
Memory: Pages/sec、Processor: % Processor Time、PhysicalDisk: Avg. Disk Queue Length、SQLServer: Buffer Cache Hit Ratio、SQLServer: Lock Waits/sec、SQLServer: Batch Requests/sec。
- 大資料分頁新法：
OFFSET ... FETCH（2012+）或 ROW_NUMBER + 覆蓋索引；避免雙重 Top 掃描。
- SP 開發規範：
- 跨庫用 View 封裝
- SET NOCOUNT ON
- 避免大交易、HOLDLOCK
- 臨時表大量資料用 SELECT INTO 避免 Log、小資料用 CREATE TABLE + INSERT
- 結束時 TRUNCATE TABLE #t; DROP TABLE #t
- 定期 sp_recompile、重建索引、更新統計資訊
總結
效能優化是系統工程：**架構先行、索引為王、寫法為輔、監控閉環**。沒有銀彈，只有持續測量、分析、調整的循環。
