Ellie's blog

未參數化的直接查詢無法重用同一個執行計劃(同樣語法但大小寫不同或是多了空格等，也無法重用同一執行計劃): 

DBCC FREEPROCCACHE

USE AdventureWorks
GO
SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = 327
SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = 328
SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = 329
SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = 330
SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = 331

SELECT TOP 30 
    cp.usecounts, 
    cp.size_in_bytes  AS size_byte,
    cp.objtype,
    cacheobjtype,
    DB_NAME(st.dbid)  AS database_name,
    st.text  AS query_text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid)  = 'AdventureWorks'

 

啟用 Optimize for Ad hoc Workloads  雖可避免計劃快取被不重複使用的已編譯計劃填滿(減輕記憶體壓力), 但非治本之法 : 

EXEC sp_configure 'optimize for ad hoc workloads', 1; 
RECONFIGURE;
DBCC FREEPROCCACHE;

SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = 327
SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = 328
SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = 329
SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = 330
SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = 331

• 第1次執行語法時，只儲存小型已編譯計劃虛設常式 (而非完整的已編譯計劃)  
  
• 第2次重覆執行同一語法時才會儲存完整的編譯計畫:
  SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = 327
  

** https://blog.sqlauthority.com/2017/10/20/sql-server-turn-optimize-ad-hoc-workloads/
這個機制對「大量、只執行過一次就再也不會出現」的Ad Hoc查詢非常有效,因為省下的是那些永遠用不到第二次的完整計畫佔用的記憶體。

若Instance的實際工作負載並不是這種模式，例如這些看似Ad Hoc的查詢文字其實會重複出現(常見於應用程式用字面值拼SQL、但同一段SQL會被反覆呼叫多次的情境)，那麼打開這個設定就完全發揮不出它該有的省記憶體效果,反而會帶來副作用:

1. 每一段查詢的第一次執行都只拿到Stub,無法命中快取重用,等於要多一次重新編譯的成本,增加CPU與編譯過程中所需的暫時記憶體(Compile本身也會消耗記憶體與Memory Grant)。
2. 原本Plan Cache裡已經存在的完整計畫並不會因為改了這個設定就被清空,新的Stub是疊加上去的,在舊計畫還沒被LRU機制淘汰掉之前,Plan Cache的整體佔用反而是「Stub + 舊完整計畫」同時存在,短期內記憶體用量不減反增。
3. 編譯次數增加,連帶讓CPU使用率上升,可能導致server was running a bit slow。

 

較建議的作法是從應用程式層改用參數化查詢:

Caching Mechanisms | Microsoft Learn Adhoc (未參數化的一次性查詢)： 每次執行 → 新計畫 → Plan Cache 膨脹 → 記憶體浪費  Prepared (已參數化的查詢)       :  第一次執行 → 建立含參數的計畫 之後每次執行 → 重複使用同一計畫 usecounts 越高 → 計畫被重複使用越多次 → 效能越好 Adhoc 查詢： SELECT * FROM Orders WHERE CustomerID = 1         ↓ SQL Server 自動參數化 Prepared 計畫：SELECT * FROM Orders WHERE CustomerID = @0         ↓ 不同的值（1, 2, 3...）都重複使用同一個計畫 Prepared  的兩種來源   1) SQL Server 自動參數化（Auto-Parameterization）   2) 應用程式明確使用 sp_executesql 或 sp_prepare

-----------------
-- Prepared 
-----------------
DBCC FREEPROCCACHE

declare @ProductID int = 327
EXEC sp_executesql
    N'SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = @ProdID',
    N'@ProdID INT',
    @ProdID = @ProductID;

declare @ProductID int = 328
EXEC sp_executesql
    N'SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = @ProdID',
    N'@ProdID INT',
    @ProdID = @ProductID;

declare @ProductID int = 329
EXEC sp_executesql
    N'SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID = @ProdID',
    N'@ProdID INT',
    @ProdID = @ProductID;

-----------------
-- SP
-----------------
Create or alter  PROC sp_selectProduct @ProductID int
AS
SELECT @@SERVERNAME, @@version,  ProductID, Name FROM [Production].[Product] WHERE ProductID =  @ProductID 

Exec sp_selectProduct @ProductID = 327
Exec sp_selectProduct @ProductID = 328
Exec sp_selectProduct @ProductID = 329
Exec sp_selectProduct @ProductID = 330
Exec sp_selectProduct @ProductID = 331