SQL Server 查詢計畫快取記憶體消耗分析：參數化查詢 vs 臨時查詢的實測對比

應用程式若大量使用非參數化臨時查詢，會導致計畫快取膨脹、記憶體壓力大增、CPU 反覆編譯。本文透過 Java 程式碼模擬 20 筆非參數化與參數化查詢，配合 DMV SYS.DM_EXEC_CACHED_PLANS 實測快取佔用差異，量化記憶體與 CPU 影響，並說明預存程序為最佳實務。

場景模擬

• 目標表：ADVENTUREWORKS.SALES.SALESORDERHEADER。
• 查詢：依 SALESORDERID 擷取訂單資料，ID 1~20。
• 對比：非參數化字串拼接 vs PREPARED STATEMENT (參數化) vs 預存程序。

非參數化臨時查詢 (錯誤示範)

// Java 片段
for (int i = 1; i <= 20; i++) {
    String sql = "SELECT * FROM SALESORDERHEADER WHERE SALESORDERID = " + i;
    stmt.executeQuery(sql);  // 每次產生新查詢文字
}

DMV 查詢快取

SELECT QS.USECOUNTS, QS.CACHEOBJTYPE, QS.OBJTYPE, QT.TEXT
FROM SYS.DM_EXEC_CACHED_PLANS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) QT
ORDER BY QT.TEXT;

結果

• 產生 20 個獨立計畫 (OBJTYPE = ADHOC)，USECOUNTS = 1。
• 每個計畫佔用 ~24KB 快取記憶體，總計 ~480KB。
• 若每秒 1000 此類查詢，快取將在數分鐘內佔用數 GB 記憶體，觸發記憶體壓力、計畫驅逐、CPU 飆升。

參數化查詢 / PreparedStatement (改善)

String sql = "SELECT * FROM SALESORDERHEADER WHERE SALESORDERID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 1; i <= 20; i++) {
    ps.setInt(1, i);
    ps.executeQuery();
}

結果

• 單一計畫 (OBJTYPE = PREPARED)，USECOUNTS = 20。
• 快取記憶體 ~24KB，CPU 編譯成本僅 1 次。
• 計畫重用率 100%。

預存程序 (最佳實務)

CREATE PROC USP_GET_ORDER @ORDERID INT
AS SELECT * FROM SALESORDERHEADER WHERE SALESORDERID = @ORDERID;

• 計畫物件類型 PROC，參數嗅探優化、權限封裝、維護性最高。
• 應用層呼叫：EXEC USP_GET_ORDER @ORDERID = ?。

三種模式量化對比

DMV 監控腳本：偵測臨時查詢氾濫

-- 找出 USECOUNTS=1 的 ADHOC 計畫 (疑似臨時查詢)
SELECT TOP 50 
    QT.TEXT, QS.USECOUNTS, QS.SIZE_IN_BYTES/1024.0 AS KB
FROM SYS.DM_EXEC_CACHED_PLANS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) QT
WHERE QS.OBJTYPE = 'ADHOC' AND QS.USECOUNTS = 1
ORDER BY QS.SIZE_IN_BYTES DESC;

-- 整體快取佔用摘要
SELECT OBJTYPE, COUNT(*) AS PLAN_COUNT, SUM(SIZE_IN_BYTES)/1024/1024 AS TOTAL_MB
FROM SYS.DM_EXEC_CACHED_PLANS
GROUP BY OBJTYPE;

最佳實務建議

1. ✅ 預設使用預存程序，封裝邏輯、權限最小化、計畫穩定。
2. ✅ 若必須動態 SQL，強制參數化 (SP_EXECUTESQL / PreparedStatement)。
3. ✅ 啟用 OPTIMIZE FOR AD HOC WORKLOADS (SQL 2008+)：首次執行僅快取計畫存根，第二次才完整快取，減少一次性查詢佔用。

   EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1; RECONFIGURE;
   EXEC SP_CONFIGURE 'OPTIMIZE FOR AD HOC WORKLOADS', 1; RECONFIGURE;

4. ✅ 定期執行 DBCC FREEPROCCACHE (維護窗口) 或針對特定計畫 DBCC FLUSHPROCINDB。
5. ✅ 監控 SQLServer:Plan Cache 計數器，設定警示於 Cache Hit Ratio < 90% 時通知。

1. 上一篇文章： SQL Server 2008 資料探勘完整流程六步驟：從定義問題到部署模型的實務指南
2. 下一篇文章： SQL Server 索引設計最佳實務：叢集/非叢集/覆蓋索引選擇策略、維護與實測效能對比

關於SqlServer

深入解析 MSSQL 高頻高併發場景下的鍵查找死鎖問題與解決方案

死鎖對資料庫管理員（DBA）與開發人員而言並不陌生，其成因多

SQL Server 2005 安全性原則深度解析：預設安全、Windows 驗證密碼策略與介面配置工具

可信任運算與 SQL Server 2005 SQL Ser

SQL Server 2012 連結伺服器與 SQL Job 實現跨伺服器同步

需求 兩台 SQL Server 2012 跨伺服器同步資料

Microsoft SQL Server 2005 Service Broker：異步消息隊列最佳實踐

核心價值：為什麼需要異步排隊操作？ 大型可擴展系統幾乎都包含