搜尋本站文章

2017-11-09

[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 executionsNumber of times the cacheCache ObjectPlan 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


03. 第 2 次執行 相同的 Adhoc Query

觀察 Plan Cache:

  • SQL Server 已經 Cache Object 為:Compiled Plan
  • 其 Obj Type 仍是 Adhoc cache。
  • Plan Size 則是使用了 16 KB。
  • Use Counts 仍是 1。

-- 108_2nd_Adhoc


04. 第 3 次執行 相同的 Adhoc Query


觀察 Plan Cache:
  • Cache Object 仍是:Compiled Plan。
  • 其 Obj Type 仍是 Adhoc cache。
  • Plan Size 仍是 16 KB。
  • Use Counts 已累加為 2

-- 109_3rd_Adhoc


05. 第 4 次執行 相同的 Adhoc Query


觀察 Plan Cache:
  • Cache Object 仍是:Compiled Plan。
  • 其 Obj Type 仍是 Adhoc cache。
  • Plan Size 仍是 16 KB。
  • Use Counts 已累加為 3
-- 110_4th_Adhoc


06. 彙整如下:

-- 111_Summary





Enable optimize for ad hoc workloads

-- 501_optimize for ad hoc workloads_針對特定工作負載最佳化


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


-- 503_Enable optimize for ad hoc workloads


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


-- 505_View Server Properties- Enabled optimize for ad hoc workloads





觀察:記憶體 Plan cache 各種物件的使用況狀

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






範例程式碼

20171107_optimize for ad hoc workloads
https://drive.google.com/drive/folders/1OJRUoIyeEScNIKbV-O4QRO_eyIy1Fzbu?usp=sharing




參考資料

Plan Cache Internals
https://msdn.microsoft.com/en-us/library/cc293624.aspx

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

針對特定工作負載最佳化伺服器組態選項
https://docs.microsoft.com/zh-tw/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option

Execution Plan Caching and Reuse - 執行計畫快取與重複使用
https://technet.microsoft.com/zh-tw/library/ms181055(v=sql.105).aspx

sys.dm_exec_cached_plans (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-cached-plans-transact-sql

Plan cache and optimizing for adhoc workloads
https://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/

Plan cache, adhoc workloads and clearing the single-use plan cache bloat
https://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/

沒有留言:

張貼留言