隨著項目規模增長，存儲過程數量激增、邏輯複雜化，缺乏統一規範將導致維護地獄；數據量爆發時，未經優化的 SP 可能比優化版慢百倍。本文匯總一線實戰經驗，構建從編寫規範到深度優化的完整體系。
原則：嚴禁在 SP 中直接引用 Database.dbo.Table 跨庫對象。
做法：在當前庫創建視圖映射外部表/視圖，SP 僅引用本地視圖。
理由：sp_depends 無法追蹤跨庫依賴，導致影響分析失效、重構風險極大。
提交前必須執行 SET SHOWPLAN_ON 或 SSMS「顯示估計執行計劃」，檢查：
| 規則 | 說明 | 反模式示例 | 正確模式 |
|---|---|---|---|
| 避免大事務 | 縮短鎖持有時間，提升並發 | 單事務處理 10 萬行更新 | 分批提交，每批 2000 行 |
| 慎用 HOLDLOCK | 序列化隔離級，極大降低並發 | 全表 HOLDLOCK 查詢 | 行版本控制/快照隔離 |
| 減少重複訪問大表 | 提取到臨時表/表變量再連接 | 多次 JOIN 同一大表 | WITH CTE AS (SELECT ... FROM BigTable) 再多次引用 |
| 避免游標 | 逐行處理極慢，超 1 萬行必改集合操作 | 游標循環內 UPDATE | 單條 UPDATE 配合 CASE/窗口函數 |
| WHERE 條件順序 | 按索引列順序、選擇性從高到低排列 | WHERE Name='A' AND ID=1 (索引為 ID,Name) | WHERE ID=1 AND Name='A' |
| 避免列運算破壞 SARG | WHERE 列兩側無函數/運算 | WHERE YEAR(DateCol)=2023 | WHERE DateCol >= '2023-01-01' AND DateCol < '2024-01-01' |
| EXISTS 替代 COUNT | 存在性判斷用 EXISTS，統計全表行數才用 COUNT | IF (SELECT COUNT(*) FROM T WHERE ...) > 0 | IF EXISTS (SELECT 1 FROM T WHERE ...) |
| >= 優於 > | 利用索引邊界查找優化 | WHERE ID > 100 | WHERE ID >= 101 |
| OR/UNION 互換優化 | OR 導致掃描，UNION ALL 分支可用索引 | WHERE A=1 OR B=2 | SELECT ... WHERE A=1 UNION ALL SELECT ... WHERE B=2 |
| 連接列類型一致 | 隱式轉換導致索引失效 | VARCHAR 列 JOIN INT 列 | 統一類型或顯式 CAST |
| 參數類型匹配 | SP 參數類型與表列完全一致 | @Param VARCHAR(20) 對應 CHAR(10) 列 | @Param CHAR(10) |
| 控制修改數據量 | 超 200 頁(400KB) 觸發鎖升級為表鎖 | 單語句 UPDATE 百萬行 | 分批、分區、延遲索引維護 |
INDEX(index_name) 強制提示。SET STATISTICS IO ON 對比邏輯讀）。sp_recompile 刷新計劃緩存。| 場景 | 建議 | 理由 |
|---|---|---|
| DISTINCT/ORDER BY/GROUP BY/JOIN | 盡量避免或限制結果集規模 | 大量排序/哈希溢寫 TempDB，引發爭用 |
| 頻繁創建/刪除臨時表 | 改用表變量或復用臨時表 | 系統表元數據鎖競爭 |
| 大量數據一次性載入臨時表 | SELECT INTO 代替 CREATE TABLE + INSERT | 最小化日誌，並行插入，速度快 |
| 小量數據/復用場景 | CREATE TABLE + INSERT | 緩解系統表壓力，統計信息更準 |
| 大臨時表需索引 | 創建表、建索引放入獨立子 SP | 確保編譯時索引元數據可用，獲得最優計劃 |
| SP 結束清理 | TRUNCATE TABLE → DROP TABLE 顯式刪除 | 避免系統表長時間鎖定，立即釋放空間 |
| 大臨時表 JOIN 大表 | 極力避免，拆分邏輯或物化中間結果 | 單語句多次訪問 TempDB 系統表，擴大爭用 |
