內嵌 SQL 與存儲過程性能對比：重編譯與計劃緩存的關鍵影響

問題現象

有時內嵌 SQL（動態 SQL）執行速度反而快於存儲過程，這違背了「存儲過程預編譯、計劃復用」的常規認知。

核心原因：存儲過程重編譯

存儲過程執行緩慢的常見原因是**頻繁重編譯**，導致無法復用執行計劃。需通過性能測試確認：

• 對比兩類查詢的 SHOWPLAN 輸出
• 監控查詢計劃緩存命中率與缺失率（Cache Hit Ratio / Cache Miss Ratio）

優化建議：指定對象擁有者前綴

在存儲過程內部引用表、視圖、預存程序時，**顯式指定擁有者（通常是 dbo）**，可顯著減少重編譯與緩存缺失：

-- 不推薦
SELECT * FROM mytable

-- 推薦：指定 dbo 擁有者
SELECT * FROM dbo.mytable

原理

• 未指定擁有者時，SQL Server 需解析默認 schema，可能因用戶不同導致計劃無法復用
• 指定 dbo. 後，計劃緩存鍵值固定，不同用戶調用可共享同一計劃
• 減少 COMPILE 鎖爭用，提升高併發下的吞吐量

其他常見導致重編譯的場景

• 臨時表結構變更（CREATE INDEX、ALTER TABLE）
• 統計信息大幅變更觸發自動更新
• SET 選項異常（如 ANSI_NULLS、QUOTED_IDENTIFIER 不一致）
• 使用 WITH RECOMPILE 或 OPTION (RECOMPILE)

診斷工具

• sp_recompile：標記對象需重編譯
• SQL Server Profiler：捕獲 SP:Recompile 事件
• DMV：sys.dm_exec_query_stats、sys.dm_exec_cached_plans 分析計劃復用情況

總結

存儲過程本身不保證性能優於動態 SQL，**計劃復用率**才是關鍵。規範使用 dbo. 前綴、穩定 SET 選項、避免臨時表 DDL，是確保存儲過程高性能的基礎功夫。