存儲程序開發規範與效能優化指南：從 SQL 寫法、索引策略到 tempdb 使用原則

適用對象

資料庫開發人員、大數據量專案、需優化 SP 效能的團隊。

核心規範與優化要點

1. 跨資料庫存取：建立檢視隔離

當前資料庫建立檢視參考他庫物件，避免直接使用 Database.dbo.Table，因 sp_depends 無法追蹤跨庫相依，影響後續稽核。

2. 提交前必做：查詢計畫分析

使用 SET SHOWPLAN ON 分析執行計畫，自行完成查詢優化檢查。

3. SQL 撰寫規範

• 避免大交易、慎用 HOLDLOCK，提升並發。
• 避免重複存取同一大表，改用暫存表暫存中間結果再連接。
• 盡量不用游標；超過 1 萬列必改寫；游標迴圈內禁用表連接。
• WHERE 條件順序依索引順序、選擇性高低排列，欄位順序與索引一致。
• WHERE 子句左邊勿做函數/運算，否則索引失效。
• 判斷存在用 EXISTS 代替 COUNT(1)；統計全表才用 COUNT(1)（比 COUNT(*) 快）。
• 優先用 >= 而非 >。
• 注意 OR 與 UNION 互換優化。
• 連接欄位資料型別一致，避免隱含轉換。
• 參數與欄位型別匹配。
• 單陳述式影響超過 200 頁 (400KB) 觸發鎖升級（頁鎖→表鎖），拆分批次。

4. 索引使用規範

• 大型 OLTP 表索引建議 ≤ 6 個，結合應用查詢模式設計。
• 優先使用索引欄位作條件，必要時 INDEX(index_name) 強制指定。
• 避免大表 Table Scan，必要時新增索引。
• 複合索引必須用到第一欄位才會生效。
• 定期重建索引、重新編譯預存程序（sp_recompile）。

5. tempdb 使用原則

• 減少 DISTINCT/ORDER BY/GROUP BY/HAVING/JOIN/COMPUTE 降低 tempdb 壓力。
• 避免頻繁建刪暫存表。
• 大量資料用 SELECT INTO 避免 Log；小量用 CREATE TABLE + INSERT 緩和系統表。
• 大暫存表需索引時，建表與建索引放入獨立子程序，確保優化器能使用索引。
• 程序結束顯式 TRUNCATE TABLE 後 DROP TABLE，減少系統表鎖定時間。
• 慎用大暫存表連接大表，單一陳述式多次存取系統表。

6. 合理演算法選擇

結合 SET STATISTICS IO/TIME ON、SHOWPLAN、ASE Tuning 手冊，多演算法比對，取資源最少、效率最高者。