2017-11-11

[SQL Server] Ad hoc Query and Reuse - 重複使用


延續前一篇 [SQL Server] Enable optimize for ad hoc workloads , 針對特定工作負載最佳化

編譯 Execution Plan

在 SQL Server 中執行任何 SQL 陳述式時，關聯式引擎會先尋找整個 Plan Cache，確認是否已經有相同的 SQL 陳述式之 Execution Plan。

  • 如果有找到，就可以重複使用，節省重新編譯 SQL 陳述式的負擔。
  • 如果沒有找到，SQL Server 會為該 SQL 陳述式建立新的 Execution Plan。

比對方式 - hash value

SQL Server 會為每一個 SQL 陳述式建立 hash value，以此 hash value 比對另一 SQL 陳述式的 hash value 是否相同。

  • 若 hash 值 相同，就能重複使用該 SQL 陳述式所建立 Execution Plan。
  • 若 hash 值 不同，就必須為該 SQL 陳述式編譯新的 Execution Plan。

這裡所指的 SQL 陳述式，包含：Ad hoc Query, Stored Procedure 等。

也就是說，要能重複使用 Execution Plan，則執行的 Ad hoc Query 必須完全相同。舉例來說，這包含了：空白、註解、大小寫、結尾符號、SET 選項 等。

要完全一模一樣的 Ad hoc Query，才能重複使用 Execution Plan。




Demo: Ad hoc Query and Reuse


01. 執行 Ad hoc Query

以下有 7 個 Ad hoc Query，前 6 個回傳相同結果。
第 7 個陳述式，僅改變 WHERE 條件式中的 OrderID=2。

-- 101_Adhoc Query


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

  • 有 5 個 Ad hoc query，判斷為無法重複使用 Execution Plan，其 Cache Object 都是Compiled Plan，個別都使用了 16 KB。
  • 僅有 1 個 Ad hoc query，判斷為可以重複使用 Execution Plan，其 Cache Object 是完整的 Compiled Plan，使用 16 KB。

-- 102_View each cached query execution plan



-- 103_View each cached query execution plan


03. 啟用「optimize for ad hoc workloads」

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

-- 104_Enable optimize for ad hoc workloads



04. 觀察 Ad hoc query 

  • 有 5 個 Ad hoc query，判斷為無法重複使用，其 Cache Object 都是「compiled plan stub(已編譯計畫虛設常式)」，個別僅使用 0.125 KB。
  • 僅有 1 個 Ad hoc query，判斷為可以重複使用 Execution Plan，其 Cache Object 是完整的 Compiled Plan，使用 16 KB。
  • 啟用 optimize for ad hoc workloads 後，SQL Server 在編譯 Adhoc query 時，採取 「compiled plan stub(已編譯計畫虛設常式)」 方式，耗用少量的 Plan Cache 資源。

-- 105_View each cached query execution plan



範例程式碼

20171110_Ad hoc Query and Reuse
https://drive.google.com/drive/folders/1iPrZdHHk812HumTcYJVSlbtZSTE9cDjO?usp=sharing


參考資料

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

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
於

沒有留言:

張貼留言