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

您的 SQL Server 應用正在浪費記憶體嗎？非參數化 Ad-hoc 查詢導致計劃緩存膨脹的元兇與對策

編輯：關於SqlServer

現象：計劃緩存被大量「一次性」佔用

應用程式最常見的效能陷阱：使用非參數化的 Ad-hoc 查詢（字串拼接 SQL）而非預備查詢或預存程序。每次執行時 WHERE 條件值不同，SQL Server 視為不同查詢，產生專屬執行計劃並存入計劃緩存，極難重用。

什麼是計劃緩存？

屬於 SQL Server 共用記憶體池一部分，儲存解析、編譯、優化後的查詢執行計劃。引擎優先尋找可重用計劃，避免重複 CPU 密集型編譯工作，降低回應延遲與 CPU 壓力。

實證：Java 迴圈發送 20 筆非參數化查詢

Java 代碼片段：非參數化查詢迴圈 — 圖 1：以字串拼接方式在迴圈中查詢前 20 筆銷售訂單

DMV 驗證：每筆查詢產生獨立非參數化計劃

SELECT qs.usecounts, cacheobjtype, objtype, qt.text
FROM sys.dm_exec_cached_plans qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS qt
ORDER BY qt.text;

計劃緩存查詢結果：大量 Ad-hoc 計劃 — 圖 2：每次執行皆產生具體值的專屬計劃，usecounts=1，無重用機會

後果：高頻應用迅速耗盡伺服器記憶體

計劃緩存膨脹擠佔資料緩存（Buffer Pool）
編譯 CPU 飆升，查詢延遲增加
可能觸發記憶體壓力導致計劃被驅逐，形成惡性循環

解決之道：參數化與重用

方案	適用場景	實作要點
預存程序	核心業務邏輯、複雜查詢	參數化天然支援，計劃重用率最高，安全性最佳
sp_executesql + 參數	動態 SQL 必須場景	以參數標記取代字串拼接，引擎可自動參數化/重用
強制參數化 (DB 選項)	無法修改應用程式碼的舊系統	`ALTER DATABASE SET PARAMETERIZATION FORCED`，需測試副作用
優化 Ad-hoc 負載 (Server 選項)	大量一次性查詢無法消除	`sp_configure 'optimize for ad hoc workloads', 1` 僅緩存計劃存根，第二次執行才存完整計劃

最佳實踐建議

新開發專案預設使用預存程序或參數化命令（ADO.NET SqlCommand.Parameters、JDBC PreparedStatement、ORM 參數綁定）。
現有系統排查 sys.dm_exec_cached_plans 中 objtype='Adhoc' 且 usecounts=1 佔比高的查詢，優先重構。
啟用「優化 Ad-hoc 負載」作為緩衝，但不可替代根本修正。
建立程式碼審查規範：禁止 SQL 字串拼接用戶輸入。

從源頭消除非參數化查詢，是釋放記憶體、降低 CPU、提升穩定性的最高 ROI 動作。