[SQL Server] Plan Cache Pollution - Hard-Coded SQL

延續前一篇  [SQL Server] Ad hoc Query and Reuse - 重複使用


Plan Cache Pollution

這是指 應用程式 執行 SQL 陳述式的方式，有可能因故讓 SQL Server 無法重複使用 Execution Plan，耗費大量的 Plan Cache 資源，污染了 Plan Cache。

本文討論 應用程式 採用 Hard-Coded SQL 陳述式，導致產生 Plan Cache Pollution。





觀察 Plan Cache 各項物件的記憶體使用情況、使用次數

-- View each plan cache type, Use Counts, Memory used
SELECT objtype AS [CacheType], COUNT_BIG(*) AS [Total Plans], 
 SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
 AVG(usecounts) AS [Avg Use Count],
 SUM(CAST((
  CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0
  END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
 SUM(
  CASE WHEN usecounts = 1 THEN 1 ELSE 0
        END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC

---

Demo
Plan Cache Pollution - Hard-Coded SQL 

01. Hard-Coded SQL Statements(硬式編碼的 SQL 陳述式) 

以下示範 .NET 應用程式，使用 Hard-Coded SQL 陳述式的方式組合傳入參數值。

• 使用 For 迴圈執行 SQL 陳述式 20,000 次，每次執行並傳入一個 Hard-Coded 的參數值。
• 使用 Hard-Coded SQL 傳入參數值 的方式，將使用每一句 SQL 陳述式的 hash value 是不同的，導致 SQL Server 無法重複使用 Execution Plan，必須額外建立各自的 Execution Plan。

以本範例來看，使用 Hard-Coded SQL 傳入參數值的作法，產生的副作用是

• 在 Plan Cache 裡將存放了 20,000 份不同的 Execution Plan，但其內容卻幾乎相同，而且這是應能重複使用，卻因 hash value 不同而無法重複使用的 Execution Plan。

-- 101_Hard-Coded SQL Statement

02. 觀察 Plan Cache

• 20,000 份判斷為無法重複使用 Execution Plan，其 Cache Object 都是Compiled Plan，個別都使用了 16 KB。

-- 102_View each cached query execution plan

03. 觀察 Plan Cache 各項物件的記憶體使用情況、使用次數等

• 每一份 Execution Plan 耗用 16 KB，20,000份 約耗費 320 MB 的 Plan Cache。

-- 103_View each plan cache type, Use Counts, total size

---

01. 啟用「optimize for ad hoc workloads」

• 無須重新啟動 SQL Server。
• 將 optimize for ad hoc workloads 設定為 1 只會影響新的計畫。
• 已經存在計畫快取中的計畫則不會受到影響。

02. 觀察 Plan Cache

• 20,000 份判斷為無法重複使用 Execution Plan，其 Cache Object 都是 「compiled plan stub(已編譯計畫虛設常式)」，個別僅使用 0.125 KB。

-- 104_View each cached query execution plan

03. 觀察 Plan Cache 各項物件的記憶體使用情況、使用次數等

• 每一份 Execution Plan 耗用 0.125 KB，20,000份 約 僅 使用 2.5 MB 的 Plan Cache。

-- 105_View each plan cache type, Use Counts, total size

---

彙總分析

觀察 optimize for ad hoc workloads 選項的影響

• 未啟用，耗用 320 MB。
• 啟用後，僅耗用 2.5 MB。

與未啟用「optimize for ad hoc workloads 」比較起來：

• 320 MB 對比 2.5 MB，兩者差距：128 倍。

---

解決方案

因為 Plan Cache Pollution 導致浪費 Plan Cache，可用的解決方案有：

• 啟用 Optimize for Ad hoc Workload
• FORCED PARAMETERIZATION
• 使用 Parameters，例如：SQLCommand.Parameters
• 改為 Stored Procedure
• DBCC FREESYSTEMCACHE

---

範例程式碼

20171112_Plan Cache Pollution

https://drive.google.com/drive/folders/12De4e2CDvkijaAb9KDfioBOC0RgF_GxY?usp=sharing

---

參考資料

Plan Caching and Recompilation in SQL Server 2012
https://msdn.microsoft.com/en-us/library/dn148262.aspx

Plan cache pollution or how important it is to properly define parameters in code
https://blogs.msdn.microsoft.com/ivandonev/plan-cache-pollution-or-how-important-it-is-to-properly-define-parameters-in-code/

How to Create and Execute SqlCommand in ADO.NET
https://code.msdn.microsoft.com/windowsdesktop/How-to-Create-and-Execute-86922261#content

SqlParameter Class
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx

Hard-Coded SQL Statements
https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/hard-coded-sql-statements

[SQL Server] Plan Cache concepts - 認識 Plan Cache
http://sharedderrick.blogspot.tw/2017/11/sql-server-plan-cache-concepts-plan.html

[SQL Server] Enable optimize for ad hoc workloads , 針對特定工作負載最佳化
http://sharedderrick.blogspot.tw/2017/11/sql-server-enable-optimize-for-ad-hoc.html

[SQL Server] Ad hoc Query and Reuse - 重複使用
http://sharedderrick.blogspot.tw/2017/11/sql-server-ad-hoc-query-and-reuse.html