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

SQL Server 預存程序開發經驗與效能優化實戰指南

編輯：關於SqlServer

前言

預存程序 (Stored Procedure, SP) 是封裝複雜業務邏輯、減少網路往返、提升安全性的核心工具。但撰寫不當會導致維護困難、效能低落。本文結合實戰經驗，從編碼規範、查詢優化、索引策略、tempdb 使用、事務管理五大維度，提供可落地的優化準則。

一、編碼與架構規範

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

避免直接使用 Database.dbo.Table 三段式命名，因 sp_depends 無法追蹤跨庫相依性，影響影響分析。在當前資料庫建立 View 映射外部表，SP 僅引用 View。

2. 上線前強制執行計畫分析

提交前必須執行 SET SHOWPLAN_ON ON 或使用 SSMS 「顯示預估執行計畫」，檢查：

是否出現 Table Scan / Index Scan 應為 Seek
預估行數與實際行數偏差是否過大 (統計資料過期)
是否有 Missing Index 建議
並行度 (MAXDOP) 是否合理

3. 命名與格式規範

物件命名採 PascalCase：usp_GetCustomerOrders
參數加 @ 前綴，區域變數加 @，全域暫存表 ##，區域暫存表 #
關鍵字大寫，縮排 4 空格，單行不超過 120 字元
區塊註解說明業務邏輯、輸入輸出、異常處理流程

二、查詢撰寫優化準則

1. 事務與鎖控制

縮小事務範圍：只包含必要的 DML，避免長事務阻塞併發。
慎用 HOLDLOCK / SERIALIZABLE：極大降低併發，改用樂觀鎖或應用層協調。
避免大批次修改觸發鎖升級：單次影響 > 5000 列 (約 200 頁/400KB) 會從頁鎖升級為表鎖。大量異動請分批 (WHILE 迴圈 + TOP 1000 + WAITFOR DELAY)。

2. 游標替代方案

集合導向思維：單一 SET-based 陳述式替代游標迴圈。
若必須逐列處理：改用 WHILE @@FETCH_STATUS = 0 + 區域暫存表，或 CLR 彙總。
游標選項：LOCAL FAST_FORWARD READ_ONLY (唯讀、單向、低開銷)。

3. WHERE 條件撰寫技巧

4. 連接與子查詢優化

確保 JOIN 欄位資料型別一致，避免隱含轉換導致索引失效。
大表關聯小表：小表驅動大表，或使用 HASH JOIN / MERGE JOIN 提示。
相關子查詢改寫為 JOIN 或 APPLY (CROSS/OUTER APPLY)。
善用 INTERSECT/EXCEPT 替代複雜 NOT IN/EXISTS。

三、索引使用規範

OLTP 核心表索引數 ≤ 6，平衡讀寫。
強制索引提示 WITH (INDEX(idx_name)) 僅作最後手段，優先修正統計資料與查詢寫法。
複合索引必須在 WHERE/JOIN/ORDER BY 中使用「最左前綴」欄位才會生效。
定期 (每日/每週) UPDATE STATISTICS 並 sp_recompile 關鍵 SP，避免參數嗅探導致計畫失效。
利用 sys.dm_db_index_usage_stats 監控未被使用的索引，定期清理。

四、tempdb 最佳實務

原則	錯誤寫法	正確寫法	說明
欄位不參與運算	WHERE YEAR(OrderDate) = 2023	WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01'	保留 SARGable，可用索引 Seek
條件順序遵循索引鍵序	WHERE ColB = 1 AND ColA = 2 (索引為 A,B)	WHERE ColA = 2 AND ColB = 1	符合最左前綴原則
範圍條件放最後	WHERE ColA > 100 AND ColB = 5	WHERE ColB = 5 AND ColA > 100	等值優先，範圍次之
EXISTS 替代 COUNT	IF (SELECT COUNT(*) FROM T WHERE ...) > 0	IF EXISTS (SELECT 1 FROM T WHERE ...)	EXISTS 遇第一筆即停止
UNION ALL 替代 UNION	SELECT ... UNION SELECT ...	SELECT ... UNION ALL SELECT ...	UNION 需排序去重，ALL 直接合併

場景	建議做法	避免做法
大量中繼資料	SELECT INTO #Temp (最小記錄，無鎖系統表)	CREATE TABLE #Temp 後 INSERT (大量時產生大量 Log)
小量中繼資料	CREATE TABLE #Temp 後 INSERT (緩解系統表爭用)	SELECT INTO (頻繁建刪系統表鎖定)
暫存表需索引	建立暫存表 → 建索引 → 插入資料，或放入獨立子 SP (確保重編譯使用新索引)	建索引後才插入大量資料 (統計資料不準)
結束清理	TRUNCATE TABLE #Temp → DROP TABLE #Temp (釋放系統表鎖)	僅 DROP 或自然結束
排序/分組/去重	評估是否可在來源端完成，或加索引避免 TempDB 溢寫	大量 DISTINCT/ORDER BY/GROUP BY 直接在 SP 內執行

五、參數嗅探與計畫重用對策

OPTION (RECOMPILE)：關鍵查詢單獨重編譯，適用參數值差異極大導致計畫不穩定。
OPTION (OPTIMIZE FOR @param = 'typical_value')：指定典型值優化。
區域變數賦值：DECLARE @local = @param; ... WHERE col = @local (優化器視為未知值，產生平均計畫)。
動態 SQL：sp_executesql 參數化，每組參數產生專屬計畫 (防止計畫膨脹)。

六、錯誤處理與日誌範本

CREATE PROCEDURE usp_Example
    @Param INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;          -- 任何錯誤自動回滾
    BEGIN TRY
        BEGIN TRANSACTION;
            -- 核心業務邏輯
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        -- 記錄錯誤至 Log 表
        INSERT INTO dbo.ErrorLog (ProcName, ErrorNumber, ErrorMessage, ErrorTime)
        VALUES (OBJECT_NAME(@@PROCID), ERROR_NUMBER(), ERROR_MESSAGE(), SYSDATETIME());
        -- 重新拋出或返回自定義代碼
        THROW; -- SQL 2012+; 舊版用 RAISERROR
    END CATCH
END