前言：效能調優系統觀
查詢緩慢成因通常集中在：缺索引/索引失效、I/O 瓶頸、鎖爭用、資料量過大、計畫劣化、硬體資源不足。調優遵循「由外而內、由粗到細」：架構 → 索引/統計 → 查詢重寫 → 設定/硬體。
架構與物理佈局 (第 1-10 條)
- 檔案分離：資料 (.mdf)、記錄 (.ldf)、TempDB、備份、索引 (檔案群組) 放置於不同物理磁碟/RAID 陣列，消除 I/O 爭用。
- 垂直/水平分表：大表拆分熱/冷欄位 (垂直) 或按時間/租戶分區 (水平/分區表)，縮小掃描範圍。
- 硬體升級優先序：記憶體 > 快速儲存 (NVMe/SSD) > CPU 核心數。SQL Server 善用記憶體緩存熱頁面。
- 索引設計鐵律：依查詢 `WHERE/JOIN/ORDER BY` 建索引；填充因子預設 (0/100) 通常最佳；窄索引優於寬索引 (少欄位、定長類型)；低選擇性欄位 (性別/狀態) 單獨建索引無效，需組合索引。
- 網路優化：應用與資料庫同機房/同 VLAN；啟用連接池；避免 `SELECT *` 拉取無用欄位。
- 記憶體配置：`Max Server Memory` 保留 4-8GB 給 OS；啟用 `Lock Pages in Memory` (企業版) 防止分頁；32位元系統啟用 AWE。
- CPU 與並行度：`Max Degree of Parallelism (MAXDOP)` 建議設為 NUMA 節點核心數 (≤8)；`Cost Threshold for Parallelism` 提高至 50+ 避免小查詢並行開銷。
- LIKE 模糊查詢：`LIKE 'abc%'` 可用索引；`LIKE '%abc'`/`%abc%` 全掃。長字串模糊搜尋必上全文索引或語意搜尋；用 `VARCHAR` 非 `CHAR` 縮短掃描長度。
- 服務分離：OLTP (核心交易) 與 OLAP (報表/分析) 物理隔離 (不同實例/AG 次要副本/同步至數據倉儲)。
- 分散式分區檢視：跨伺服器水平分區 (Sharding) 邏輯封裝，應用端透明訪問。
維護與設定 (第 11-15 條)
- 索引維護排程：`ALTER INDEX ... REORGANIZE` (碎片 10-30%) / `REBUILD` (>30%，`ONLINE=ON`)；`UPDATE STATISTICS` 配合 `FULLSCAN` 或抽樣；關閉 `Auto Shrink`，謹慎用 `Auto Growth` (預設增長量改固定 MB，非百分比)。
- 事務管理：`COMMIT` 提交當前事務，`ROLLBACK` 回滾全部。動態 SQL 交易控制放外層 (`BEGIN TRAN ... COMMIT`)，或改寫為預存程序。
- 限制結果集：`WHERE` 子句下推；`SELECT TOP n` / `OFFSET-FETCH` (2012+) / `SET ROWCOUNT` (舊版) 避免全表傳輸；大表分頁用 Keyset Pagination (Seek 法) 替代 Offset。
- 查詢總督：`SET QUERY_GOVERNOR_COST_LIMIT 300` (秒) 自動殺掉高成本查詢；`SET LOCK_TIMEOUT` 防無限等待。
- 追蹤與分析：Profiler / Extended Events (XEvents) 捕獲長時間查詢；`SET STATISTICS IO/TIME ON` 分析邏輯讀/CPU；查看執行計畫 (估算 vs 實際)。
T-SQL 寫法優化 (第 16-35 條)
- 拒絕游標：集合思維替代逐行處理；臨時表/表變數/CTV/子查詢/CASE 表達式；若必須游標：`FAST_FORWARD` (唯讀/順向) + `READ_ONLY` 並發選項。
- UNION ALL > UNION：`UNION` 隱含 `DISTINCT` 排序去重，極耗資源；業務允許重複時必用 `ALL`。
- 慎用 DISTINCT/ORDER BY：能在應用層排序/去重就移至應用層；大結果集排序極耗 TempDB/記憶體。
- 不必要的行/列：嚴禁 `SELECT *`；明確列出欄位，利用覆蓋索引 (Covering Index) 避免 Key Lookup (RID/Clustered Index Lookup)。
- SARGable 寫法 (索引可用性)：
- 避免 `WHERE ISNULL(col,0)=0` / `WHERE YEAR(date)=2023` / `WHERE col+1=10` → 改 `WHERE col IS NULL OR col=0` / `WHERE date >= '20230101' AND date < '20240101'` / `WHERE col=9`。
- 避免 `!=` `<>` `NOT IN` `NOT EXISTS` `NOT LIKE` → 改 `NOT EXISTS` (反連接) 或 `LEFT JOIN ... WHERE right.key IS NULL`。
- `IN` / `OR` 可能導致掃描 → 改 `UNION ALL` 分支各走索引，或 `EXISTS`。
- IN 列表優化：高頻值放前面；大量值 (超過幾百) 改用臨時表/表值參數 `JOIN`。
- BETWEEN vs IN：連續範圍 `BETWEEN` 優於離散 `IN` (優化器更易用索引範圍掃描)。
- 臨時表策略：
- 大量資料導入臨時表：`SELECT INTO #Tmp ...` (最小記錄，快) + 事後建索引。
- 小資料/頻繁操作：`CREATE TABLE #Tmp ... INSERT` (減輕系統表鎖)。
- 大臨時表必建索引 → 放入子預存程序 (延遲編譯，確保索引統計生效)。
- 用畢 `TRUNCATE TABLE #Tmp; DROP TABLE #Tmp;` 釋放系統表鎖。
- 表變數 vs 臨時表：表變數 (`@Table`) 無統計資訊 (估算 1 行)，適合極小集合 (<100 行)；臨時表 (`#Table`) 有統計，適合中大集合、需索引、需並行。
- 計算欄位索引化 (2000+)：確定性運算式 + `PERSISTED` + `ANSI_NULLS ON` 等設定 → 可建索引，將運算從查詢時移至寫入時。
- 二進位大物件 (BLOB) 寫入：預存程序 + 參數化 (`@Image VARBINARY(MAX)`) 避免應用層 Base64/十六進位轉換雙倍記憶體/網路開銷。
- 聚合函數選擇：`COUNT(1)` ≈ `COUNT(*)` (優化器同樣處理)；`COUNT(NullableCol)` 忽略 NULL；判斷存在性 `EXISTS (SELECT 1 ...)` 遠快於 `COUNT(*) > 0`。
- 避免重複函數呼叫：`DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)` 存入變數重用。
- 批次處理：單一陳述式更新萬筆優於迴圈逐筆；大批次分塊 (如 5000 行) 避免長鎖/記錄檔暴漲。
- 鎖順序一致性：所有預存程序以相同順序存取表 (如先 Parent 後 Child) → 杜絕死鎖根源。
- 查詢條件順序：最選擇性 (過濾率最高) 條件優先；驅動表小結果集優先；`JOIN` 條件緊跟 `ON`，過濾條件放 `WHERE`。
- 動態 SQL 參數化：`sp_executesql @sql, N'@p int', @p=1` 重用計畫，防注入；避免 `EXEC (@sql)` 字串拼接。
進階與監控 (第 36-50 條)
- SELECT INTO 鎖系統表：長時間阻塞 `sysobjects`/`sysindexes` → 改 `CREATE TABLE + INSERT` 或表變數。
- GROUP BY/HAVING 推後：`WHERE` 先過濾 → `GROUP BY` 分組 → `HAVING` 篩選分組；純分組無聚合用 `DISTINCT` 更快。
- 視圖嵌套慎用：嵌套視圖阻礙優化器推導，隱藏邏輯；核心路徑改用預存程序/內嵌表值函數 (iTVF)。
- 效能監控計數器：
- `Memory: Pages/sec` 持續高 → 記憶體壓力。
- `Processor: % Processor Time` > 95% → CPU 瓶頸。
- `PhysicalDisk: Avg. Disk Queue Length` > 2x 磁碟數 → I/O 瓶頸。
- `SQLServer: Buffer Cache Hit Ratio` < 90% (穩態) → 記憶體不足/查詢掃描過多。
- `SQLServer: Lock Waits/sec` 高 → 阻塞/死鎖頻發。
- 隱含轉換殺手：`WHERE IntCol = '123'` → 強制轉換導致索引失效，改 `WHERE IntCol = 123`；字串比較注意定序衝突。
- 參數嗅探：首次編譯參數值非典型 → 計畫不適合後續參數。對策：`OPTION (RECOMPILE)` / `OPTIMIZE FOR (@p = 'typical')` / 區域變數賦值 / Plan Guide / 強制參數化。
- 檢查約束 vs 觸發器：`CHECK/FOREIGN KEY/DEFAULT` (引擎內部) 效能遠優於 `TRIGGER` (上下文切換)；業務邏輯能下推到約束就下推。
- 填充因子策略：高更新表 (頁分裂頻繁) 設 `FILLFACTOR = 80-90`；唯讀/少更新表用 `100` (預設) 最大化密度。
- 統計資訊自動更新：`AUTO_UPDATE_STATISTICS ON` (預設)；大表啟用 `AUTO_UPDATE_STATISTICS_ASYNC ON` 避免查詢等待統計更新；定期手動 `FULLSCAN` 關鍵表。
- 查詢存儲 / Plan Forcing (2016+)：啟用 Query Store，追蹤計畫變更，一鍵強制優良計畫 (替代 Hint)。
- 資料壓縮 (企業版)：`ROW` / `PAGE` 壓縮節省 40-80% 空間，I/O 減少換取微量 CPU，OLAP/歸檔表極大收益。
- 稀疏欄位/資料行集合：大量 NULL 欄位用 `SPARSE` + `COLUMN_SET` (XML 存取)，極大降低儲存。
- 彈性查詢 / PolyBase：查詢外部資料 (Oracle/Hadoop/S3/Azure Blob) 如本地表，避免 ETL 搬運。
總結：調優檢查清單 (每日/每週/每月)
- 【日】長查詢 Top 10 (XEvents) → 執行計畫分析 → 索引/查詢微調。
- 【週】索引碎片報告 → 重建/重組；統計資訊更新狀態；備份/還原演練；磁碟空間趨勢。
- 【月】`MAXDOP`/`CTFP` 參數回顧；資料庫檔案群組佈局優化；容量規劃；安全稽核 (權限/加密/審計)。
