德瑞克：SQL Server 學習筆記: [SQL Server] Ad hoc Query and Reuse

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