德瑞克：SQL Server 學習筆記: [SQL Server] Plan Cache Pollution - Hard-Coded SQL

2017-11-12

[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

沒有留言:

張貼留言