SQL Server 存儲過程開發規範與優化實戰：從 SQL 寫法、索引策略到 tempdb 管理

針對大數據量、高併發、高效能要求的專案，SP 開發需遵循嚴格規範並持續優化。優化後 SP 效能可較差 SP 提升數百倍。

適用對象

資料庫開發工程師、架構師、對資料庫效能有濃厚興趣的技術人員。

開發規範

1. 跨庫訪問建立 View：禁止直接引用 database.dbo.table，sp_depends 無法追蹤跨庫相依性，影響影響分析。
2. 提交前必跑 Showplan：SET SHOWPLAN_ON 分析查詢計劃，自查索引缺失、掃描、隱式轉換等問題。

SQL 撰寫優化要點

• 避免大事務、慎用 HOLDLOCK，提升併發。
• 減少重複訪問大表，先篩選進暫存表再連接。
• 盡量不用游標；超過 1 萬行必改寫；游標中嚴禁再連表。
• WHERE 條件順序遵循索引順序、範圍由大到小。
• WHERE 子句「=」左邊不可加函數/運算，否則索引失效。
• 存在性判斷用 EXISTS 代替 COUNT(1)；統計全表行數才用 COUNT(1)（優於 COUNT(*)）。
• 優先用 >= 替代 >（利用索引邊界）。
• 注意 OR 與 UNION ALL 互換、連接欄位資料型別一致、參數與欄位型別匹配。
• 單陳述式影響超過 200 頁（400KB）會觸發鎖升級（頁鎖→表鎖），大批次操作需分批。

索引使用規範

• OLTP 大表索引 ≤ 6 個，結合應用查詢模式設計。
• 優先用索引欄位作條件，必要時 INDEX(index_name) 強制指定。
• 避免大表 Table Scan，必要時新增索引。
• 聯合索引必須用到第一欄才會生效。
• 定期重建索引、更新統計、重新編譯 SP。

tempdb 管理鐵律

• 減少 DISTINCT, ORDER BY, GROUP BY, HAVING, JOIN, COMPUTE 降低 tempdb 壓力。
• 避免頻繁建刪暫存表，消耗系統表資源。
• 大量資料進暫存表用 SELECT INTO 避免記錄 Log 提速；小量用 CREATE TABLE + INSERT 緩解系統表鎖定。
• 大暫存表需建索引時，將建表與建索引放入獨立子 SP，確保編譯時索引統計可用。
• SP 結束時顯式 TRUNCATE TABLE → DROP TABLE 清理暫存表，避免系統表長時間鎖定。
• 慎用大暫存表與大表連接/修改，單語句多次存取 tempdb 系統表。

演算法選擇與調優工具

結合 SET STATISTICS IO ON, SET STATISTICS TIME ON, SET SHOWPLAN ON 等 ASE 調優命令，多種演算法實測比較，選資源消耗最少、效率最高者。