萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 查詢計畫快取:非參數化臨時查詢如何耗盡記憶體

最常見的效能殺手:非參數化臨時查詢

應用程式碼中最普遍的錯誤,就是直接拼接字串發送 非參數化臨時查詢,而非使用參數化查詢或預存程序。這類查詢會導致計畫快取膨脹,佔用大量共用記憶體池,最終拖垮整個伺服器效能。

什麼是計畫快取?

計畫快取佔用 SQL Server 共用記憶體池的一部分,儲存經過解析、編譯、優化後的查詢執行計畫。當相同查詢再次執行時,引擎會嘗試重用現有計畫,避免重複的 CPU 密集型編譯工作,從而降低延遲與 CPU 壓力。

實測:Java 迴圈發送 20 筆非參數化查詢

程式碼對 AdventureWorks.SalesOrderHeader 依序查詢前 20 筆訂單,每筆 WHERE SalesOrderID = N 的值不同。


圖一:應用程式碼片段

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;


圖二:快取中出現 20 個唯一計畫,usecounts = 1,零重用

修正:改用參數化查詢

清除快取 DBCC FREEPROCCACHE,改用 PreparedStatement 重跑相同邏輯。


圖三:參數化查詢程式碼


圖四:單一計畫 usecounts = 20,完美重用

為什麼這很重要?

  • 記憶體節省:少數熱門查詢佔用固定空間,騰出空間給資料/索引快取(Buffer Pool)。
  • CPU 降溫:編譯是 CPU 密集操作,重用計畫可大幅降低編譯頻率。
  • 穩定性:避免計畫快取擠佔導致計畫逐出、重新編譯風暴。

進階建議:優先採用預存程序

雖然參數化查詢已大幅改善,但預存程序仍具備額外優勢:

  1. 安全性:封裝資料表存取,防止 SQL 注入與直接資料表操作。
  2. 維護性:業務邏輯集中於資料庫層,前端異動較小。
  3. 計畫穩定:預存程序計畫更易被重用與優化。

結語

無論採用參數化查詢或預存程序,核心原則只有一個:確保查詢計畫可重用。這是解決記憶體與 CPU 壓力、提升應用程式擴展性的基石。

關鍵字: SQL Server、計畫快取、參數化查詢、非參數化查詢、PreparedStatement、DBCC FREEPROCCACHE、dm_exec_cached_plans

copyright © 萬盛學電腦網 all rights reserved