您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 預存程序編寫與效能優化規範：從 SQL 寫法、索引使用到 TempDB 管理的完整檢查清單

編輯：關於SqlServer

預存程序 (SP) 是資料庫邏輯封裝核心，但撰寫不當極易成為效能殺手。本文針對大數據量、高併發系統，提供涵蓋 **SQL 寫作規範、索引使用規範、TempDB 使用規範、演算法選擇** 四大維度的詳細優化檢查清單，並輔以具體語法範例與原理說明，助開發人員產出高效能、可維護的 SP 程式碼。

一、適用對象與前置條件

資料庫開發人員、架構師、效能調優專家。
專案包含大量資料、複雜業務邏輯、對 SP 效能有嚴格要求。
**提交前必做**：啟用 `SET SHOWPLAN_ON` / `SET STATISTICS IO, TIME ON` 分析執行計畫、I/O 與耗時，完成自我優化檢查。

二、 SQL 寫作規範 (效能與並發核心)

規則	說明與範例	違反後果
避免大交易、慎用 HOLDLOCK	縮短交易範圍，提交後立即釋放鎖。`HOLDLOCK` 等同 `SERIALIZABLE`，極大降低並發。	阻塞鏈、死結、鎖升級為表鎖。
避免重複存取同一大表	將篩選條件先查入暫存表 (`#temp` 或 `@table`)，再與其他表聯結。減少大表重複掃描。	CPU/I/O 浪費、鎖持有時間拉長。
盡量避免游標	游標逐列處理 (RBAR) 效能極差。>1萬列必須改寫為集合操作 (JOIN, EXISTS, 窗口函數)。若必須用，避免游標迴圈內再 JOIN 表。	執行時間指數級增長、鎖爭用劇增。
WHERE 條件順序優化	依索引欄位順序、選擇性高 (範圍小) 在前撰寫。最佳化器會自動重排，但明確順序利於計畫穩定。	可能導致索引搜尋變掃描、計畫不穩定。
WHERE 子句左邊禁用函數/運算	❌ `WHERE DATEADD(day, 1, Col) = @date` → 索引失效 (Non-SARGable) ✅ `WHERE Col = DATEADD(day, -1, @date)` → 索引有效	全表掃描、CPU 飆升。
存在性檢查用 EXISTS 而非 COUNT	`IF EXISTS (SELECT 1 FROM T WHERE ...)` 遇第一筆即停止。 `SELECT COUNT(1)` 需掃描所有符合列。 `COUNT(1)` > `COUNT(*)` (微幅)。	不必要的全掃描。
優先用 >= 而非 >	`WHERE Id >= 100` 可用索引 Seek 定位 100；`WHERE Id > 100` 需 Seek 100 再移動一步 (極微差，但習慣統一)。	微幅效能差異。
OR 改寫為 UNION ALL	`WHERE A=1 OR B=2` → 索引通常失效。改寫：`SELECT ... WHERE A=1 UNION ALL SELECT ... WHERE B=2` (注意去重)。	掃描替代搜尋。
聯結欄位資料型別一致	`VARCHAR` JOIN `NVARCHAR`、`INT` JOIN `BIGINT` → 隱性轉換導致索引失效。統一型別、長度、定序。	隱性轉換、計畫警告、掃描。
參數與欄位型別匹配	SP 參數型別必須與資料表欄位型別完全一致，避免參數嗅探導致計畫不穩或轉換失效。	參數嗅探、型別轉換。
控制單次 DML 影響行數	單陳述式影響 > 200 頁 (400KB) → 鎖升級 (頁鎖→表鎖)。大批次更新/刪除分批處理 (每批 1000-5000 列)。	長時間表鎖、阻塞所有存取。

三、索引使用規範

OLTP 表索引數 ≤ 6：平衡讀寫，過多索引拖慢 DML。
強制索引提示：必要時 `WITH (INDEX(IndexName))`，但慎用 (綁死計畫)。
避免 Table Scan：大表無索引查詢 → 必建索引。
複合索引首欄必用**：`INDEX (A,B,C)` 查詢必含 `A`，否則索引不被使用。
週期性維護**：`REBUILD` 碎片 > 30%、`UPDATE STATISTICS`、`sp_recompile` 刷新計畫。

四、 TempDB 使用規範 (系統資源保護)

規則	說明
減少排序/雜湊運算子	`DISTINCT`, `ORDER BY`, `GROUP BY`, `HAVING`, `JOIN` (Hash Match), `COMPUTE` 皆大量使用 TempDB。能在應用層/索引排序解決者優先避免。
避免頻繁建立/刪除暫存表	消耗系統表 (`sysobjects`, `syscolumns`) 鎖與鎖存。改用表變數 (`@t`) 或重複使用暫存表** (`TRUNCATE TABLE #t`)。
大量資料用 SELECT INTO	單次大量載入 (`INSERT INTO #t SELECT ... FROM BigTable`) 用 `SELECT INTO #t ...` 最小化記錄 (Minimal Logging)，速度快、Log 少。小量資料用 `CREATE TABLE #t + INSERT` 緩和系統表壓力。
暫存表需索引時獨立子程序	建立 `#t` → 建立索引 → 使用。放在獨立子 SP 中，確保編譯時索引統計資訊存在，主 SP 能正確使用索引 (避免延遲編譯/重編譯問題)。
SP 結束顯式清理暫存表	`TRUNCATE TABLE #t; DROP TABLE #t;` 釋放系統表鎖定，避免長時間佔用 TempDB 元資料鎖。
慎用大暫存表 JOIN 大表	單陳述式多次存取 TempDB 系統表，嚴重爭用。改用永久表或分批處理。

五、演算法選擇與驗證工具

同一業務邏輯嘗試**多種演算法** (如: 暫存表 vs CTE vs 窗口函數 vs 遞歸 CTE vs 應用層分頁)。

使用調優命令量化比較：


  SET STATISTICS IO ON;      -- 邏輯/物理讀取頁數
  SET STATISTICS TIME ON;    -- CPU/經過時間
  SET SHOWPLAN_ON;           -- 估計執行計畫 (不執行)
  SET STATISTICS PROFILE ON; -- 實際執行計畫 (含實際行數)

選擇 **資源消耗最少、執行時間最短、計畫穩定** 的方案上線。

六、總結：SP 優化提交清單


[ ] 無 SELECT *，明確列出欄位
[ ] 無函數包裹索引欄位 (SARGable)
[ ] 存在性檢查用 EXISTS
[ ] 大表重複存取 → 暫存表預篩選
[ ] 無游標 / 游標內無 JOIN
[ ] 複合索引首欄覆蓋核心查詢
[ ] 參數型別 = 欄位型別
[ ] 大批次 DML 分批 (避免鎖升級)
[ ] TempDB 壓力操作最小化
[ ] SHOWPLAN/STATISTICS IO 驗證通過
[ ] 註釋說明複雜邏輯、優化考量