德瑞克：SQL Server 學習筆記: [SQL Server] Enable optimize for ad hoc workloads , 針對特定工作負載最佳化

SQL Server 2008 提供了「optimize for ad hoc workloads (針對特定工作負載最佳化)」

優點

啟用 optimize for ad hoc workloads 後，SQL Server 可以更有效率的使用 Plan Cache。
避免發生 Plan Cache 存放了不會重複使用的 Execution Plan，可減輕記憶體不足的壓力問題。

功能

啟用 optimize for ad hoc workloads 後，SQL Server 在編譯 Adhoc Query 時，採取「compiled plan stub(已編譯計畫虛設常式)」方式，耗用少量的 Plan Cache 資源。

運作方式

第 1 次編譯，僅是存放 compiled plan stub。
第 2 次再度被叫用，SQL Server 就會編譯此批次，並移除先前的 compiled plan stub，並將完整的執行計畫(Execution Plan) 存放到 Plan Cache。
第 3 次再度被叫用，就能重複使用此 Adhoc cache。

副作用 side-effect

啟用 optimize for ad hoc workloads 後，將導致每個 Adhoc Query 的 Execution Plan 都需要 CPU 資源編譯到第 2 次後，才會存放到 Plan Cache。
換句話說，每個 Adhoc Query 多增加 1 次 CPU 編譯資源的耗用，要反覆執行到第 3 次，方能重複使用此 Adhoc cache。

注意事項

預設沒有啟用。
可以直接啟用 optimize for ad hoc workloads，無須重新啟動 SQL Server 服務。
啟用後，只影響到新建立的 Execution Plan。已經存在 Plan Cache 的 Execution Plan 不受影響。

「compiled plan stub(已編譯計畫虛設常式)」

具有唯一的 sql handle 和 plan handle。
compiled plan stub 沒有相關聯的 Execution Plan，因此查詢 plan handle 將不會傳回 XML 執行程序表。
Database Engine 會在首次編譯批次時，將小型「compiled plan stub(已編譯計畫虛設常式)」儲存在 Plan Cache 中，而非存放完整的已編譯計畫。
避免發生在 Plan Cache 存放了不會重複使用的 Execution Plan，可減輕記憶體不足的壓力問題。

基礎觀念

依照預設值，SQL Server 執行 Adhoc Query 時，是採取完整編譯(compiled plan)的方式來建立 Execution Plan 存放到 Plan Cache 裡。

也就是說，即便是這 Adhoc Query 久久才執行一次(一次性)，仍像是預存程序一般，使用 Plan Cache 的資源。

當遇到記憶體不足時

SQL Server 使用以成本為基礎(cost-based approach)的方式來判斷要由 Plan Cache 裡移除那些 Execution Plan。
優先移除其成本為 0 的 Execution Plan，而其他 Execution Plan 的成本將被減半。
Adhoc Query 所建立的 Execution Plan，其成本設定為 0，若有被重新使用，成本將加 1。

彙總分析

彙整如下：

Number of executions	Number of times the cache	Cache Object	Plan Size(KB)
1st	1	Compiled Plan Stub	0.125
2nd	1	Compiled Plan	16
3rd	2	Compiled Plan	16
4th	3	Compiled Plan	16

啟用「optimize for ad hoc workloads (針對特定工作負載最佳化)」後，執行相同的 Adhoc Query：

第 1 次，小型、非完整的「compiled plan stub(已編譯計畫虛設常式)」，僅使用 0.125 KB。
第 2 次，編譯為完整的 compiled plan，使用 16 KB。
第 3 次，Use Counts 累加為 2，已能重複使用此 Adhoc cache。
第 4 次，Use Counts 累加為 3，再度使用此 Adhoc cache。

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

16 KB 對比 0.125 KB，兩者差距：128 倍。

Demo: optimize for ad hoc workloads

示範版本：SQL Server 2017 Enterprise Edition

工作1：觀察 Adhoc Query

01. 執行一段 Adhoc 查詢，尚未啟用「optimize for ad hoc workloads 」

-- 101_Adhoc Query

02. 第一次執行 Adhoc Query，觀察：

SQL Server 已經建立 Cache，Cache Object 是：Compiled Plan。
其 Obj Type 是 Adhoc cache。
Plan Size 使用 16 KB。

-- 102_View each cached query execution plan

03. 檢查是否有啟用「optimize for ad hoc workloads」

預設：沒有啟用。

-- 103_View Server Properties - optimize for ad hoc workloads

工作2：啟用「optimize for ad hoc workloads」

01. 啟用「optimize for ad hoc workloads」

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

-- 104_Enable optimize for ad hoc workloads

02. 檢查確認已啟用「optimize for ad hoc workloads」

-- 105_View Server Properties - optimize for ad hoc workloads

工作3：觀察 Adhoc Query - 啟用「optimize for ad hoc workloads」

01. 第 1 次執行 Adhoc Query

注意事項：

請勿於 Production 環境執行：清空「plan cache(計畫快取)」、清空「buffer pool(緩衝集區)」。

-- 106_1st_Adhoc Query

02. 觀察 Plan Cache：

SQL Server 已經建立 Cache，Cache Object 是：Compiled Plan Stub。
其 Obj Type 是 Adhoc cache。
Plan Size 使用 0.125 KB。
Use Counts 是 1。

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

16 KB 對比 0.125 KB，兩者差距：128 倍。

「compiled plan stub(已編譯計畫虛設常式)」」

具有唯一的 sql handle 和 plan handle。
compiled plan stub 沒有相關聯的 Execution Plan，因此查詢 plan handle 將不會傳回 XML 執行程序表。
Database Engine 會在首次編譯批次時，將小型「compiled plan stub(已編譯計畫虛設常式)」儲存在 Plan Cache 中，而非存放完整的已編譯計畫。
避免發生在 Plan Cache 存放不會重複使用的 compiled plan，可減輕記憶體不足的壓力問題。

-- 107_Compiled Plan Stub