搜尋本站文章

2017-11-15

[Performance Tuning] Use SqlParameter, specified SqlDbType and length - Avoid Plan Cache Pollution


使用 SqlParameter - Add(String, SqlDbType, Int32) ,請記得指定 資料長度,可以提升效能
  1. 以效能角度來分析,明確指定 資料類型、資料長度,.NET 程式無須額外判斷,節省資源。
  2. 讓 SQL Server 能使用 Parameterization(參數化),重複使用 Execution Plan 的作法, 有效使用系統資源。
  3. 避免發生 Plan Cache Pollution。

.NET 程式使用 4 種不同的方式,以 For 迴圈執行 1,001 次的 SQL 陳述式,彙整如下:

Method Reuse Execution Plan Cache Type Plan Cache(MB) Execution Time(sec)
Hard-Coded SQL No Adhoc 7.8203125 2.5005660
AddWithValue() Partial Prepared 0.28125 0.1603226
Add(String, SqlDbType) - without length Partial Prepared 0.28125 0.1419786
Add(String, SqlDbType, Int32) Yes Prepared 0.070312 0.1399672



SqlParameter - Add(String, SqlDbType, Int32) Method








Demo -
Avoid Plan Cache Pollution - Use SqlParameter, given the specified SqlDbType and size.

示範環境

  • SQL Server 2017 Enterprise Edition
  • VS 2015

A. Hard-Coded SQL Statements

01. Hard-Coded SQL Statements(硬式編碼的 SQL 陳述式) 

以下示範 .NET 應用程式,使用 Hard-Coded SQL 陳述式的方式組合傳入參數值。

  • 使用 For 迴圈執行 SQL 陳述式 1,001 次,每次執行並傳入一個 Hard-Coded 的參數值。
  • 使用 Hard-Coded SQL 傳入參數值 的方式,將使用每一句 SQL 陳述式的 hash value 是不同的,導致 SQL Server 無法重複使用 Execution Plan,必須額外建立各自的 Execution Plan。
  • 在 Plan Cache 裡將存放了 1,001 份不同的 Execution Plan,但其內容卻幾乎相同,而且這是應能重複使用,卻因 hash value 不同而無法重複使用的 Execution Plan。

-- 011_Hard-Coded SQL



02. 觀察 Plan Cache: 各項物件的記憶體使用情況、使用次數等

以本範例來看,使用 Hard-Coded SQL 傳入參數值的作法,產生的副作用是
  • 1,001 份判斷為無法重複使用 Execution Plan,其 Cache Object 都是 Compiled Plan,個別都使用了 8 KB。
  • 每一份 Execution Plan 耗用 8 KB,CacheType 是 Adhoc1,001 份約耗費 7.8203125 MB 的 Plan Cache。

-- 012_View_cache_execution_plan_use_count_total_size




B. SqlParameter - AddWithValue() Method

01. 使用 SqlParameter - AddWithValue() 參數化查詢

以下示範 .NET 應用程式,使用 SqlParameter - AddWithValue() 參數化查詢。

  • 使用 For 迴圈執行 SQL 陳述式 1,001 次,每次執行並傳入一段由 AddWithValue() 所組建的 SQL 陳述式。
  • 使用 AddWithValue(),耗用 .NET 資源去判斷 資料類型、長度等。
  • SQL Server 在編譯由 AddWithValue() 所組建的 SQL 陳述式時,並未能完全有效重複使用。

-- 021_AddWithValue



02. 觀察 Plan Cache: 各項物件的記憶體使用情況、使用次數等

以本範例來看,使用 SqlParameter - AddWithValue() 參數化查詢的作法,SQL Server 已能 初步 採用 Parameterization(參數化) 方式來執行。

優點:
  • 增加關聯式引擎將新的 SQL 陳述式 與 先前編譯之現有 Execution Plan 配對的能力。
  • 重複使用先前 SQL 陳述式 所編譯的 Execution Plan。

整理 Parameterization(參數化) 執行的方式

Parameter Value Parameter Data Types Use Counts Obj Type
1~9 @ctn1 nvarchar(1) 9 Prepared
10~99 @ctn1 nvarchar(2) 90 Prepared
100~999 @ctn1 nvarchar(3) 900 Prepared
1000~1001 @ctn1 nvarchar(4) 2 Prepared

缺點:

  • 執行 1,001 次,SQL Server 依據傳入的資料類型、資料長度,分類為 4 種:nvarchar(1), nvarchar(2), nvarchar(3), nvarchar(4),編譯為 4 種不同的 Execution Plan。
  • 這 1,001 份 SQL 陳述式,分類為這 4 種參數,並且 初步 能重複使用這些先前 所編譯的 Execution Plan。
  • 每一份 Execution Plan 耗用 72 KB,CacheType 是 Prepared4 份約耗費 0.28125 MB 的 Plan Cache。Avg Use Count 是 250 次。

-- 022_View_cache_execution_plan_use_count_total_size




C. SqlParameter - Add(String, SqlDbType) - without length

01. 使用 SqlParameter - Add(String, SqlDbType) 參數化查詢,但 不 指定資料長度

以下示範 .NET 應用程式,使用 SqlParameter - Add(String, SqlDbType) 參數化查詢。

  • 使用 For 迴圈執行 SQL 陳述式 1,001 次,每次執行並傳入一段由 Add(String, SqlDbType) 所組建的 SQL 陳述式。
  • 使用 Add(String, SqlDbType),耗用 .NET 資源去判斷 資料長度等。
  • SQL Server 在編譯由 Add(String, SqlDbType) 所組建的 SQL 陳述式時,並未能 完全有效重複使用。

-- 031_Add_SqlDbType


02. 觀察 Plan Cache: 各項物件的記憶體使用情況、使用次數等

以本範例來看,使用 SqlParameter - Add(String, SqlDbType) 參數化查詢的作法,SQL Server 已能 初步 採用 Parameterization(參數化) 方式來執行。

在 Plan Cache 的使用上,與 B. AddWithValue() Method 相同。

-- 032_View_cache_execution_plan_use_count_total_size




D. SqlParameter - Add(String, SqlDbType, Int32)

01. 使用 SqlParameter - Add(String, SqlDbType, Int32) 參數化查詢,明確指定 資料長度

以下示範 .NET 應用程式,使用 SqlParameter - Add(String, SqlDbType, Int32) 參數化查詢。

  • 使用 For 迴圈執行 SQL 陳述式 1,001 次,每次執行並傳入一段由 Add(String, SqlDbType, Int32) 所組建的 SQL 陳述式。
  • 使用 Add(String, SqlDbType, Int32),已事先指定 資料類型、資料長度無須耗用  .NET 資源去判斷。
  • SQL Server 在編譯由 Add(String, SqlDbType, Int32) 所組建的 SQL 陳述式時,能有效使用 Parameterization(參數化) 來重複執行。


-- 041_Add_SqlDbType_Int32


02. 觀察 Plan Cache: 各項物件的記憶體使用情況、使用次數等

以本範例來看,使用 SqlParameter - Add(String, SqlDbType, Int32) 參數化查詢 的作法,SQL Server 能有效採用 Parameterization(參數化) 方式來執行。

優點:
  • 增加關聯式引擎將新的 SQL 陳述式 與 先前編譯之現有 Execution Plan 配對的能力。
  • 重複使用先前 SQL 陳述式 所編譯的 Execution Plan。

整理 Parameterization(參數化) 執行的方式

Parameter ValueParameter Data TypesUse CountsObj Type
1~1001@ctn1 nvarchar(25)1001Prepared

分析
  • 執行 1,001 次,由於 .NET 已事先定義好 資料類型、資料長度是 nvarchar(25),SQL Server 僅需編譯 1 份 Execution Plan。
  • 這 1,001 份 SQL 陳述式,能重複使用先前 所編譯的 Execution Plan。
  • 這 1 份 Execution Plan 耗用 72 KB,CacheType 是 Prepared,這 1 份約耗費 0.070312 MB 的 Plan Cache。Avg Use Count 是 1,001 次。

-- 042_View_cache_execution_plan_use_count_total_size




彙總分析

使用 SqlParameter - Add(String, SqlDbType, Int32) ,請記得指定 資料長度,可以 提升效能
  1. 以效能角度來分析,明確指定 資料類型、資料長度,.NET 無須額外判斷,節省資源。
  2. 讓 SQL Server 能使用 Parameterization(參數化),重複使用 Execution Plan 的作法, 有效使用系統資源。
  3. 避免發生 Plan Cache Pollution。

.NET 程式使用 4 種不同的方式,以 For 迴圈執行 1,001 次的 SQL 陳述式,彙整如下:

Method Reuse Execution Plan Cache Type Plan Cache(MB) Execution Time(sec)
Hard-Coded SQL No Adhoc 7.8203125 2.5005660
AddWithValue() Partial Prepared 0.28125 0.1603226
Add(String, SqlDbType) - without length Partial Prepared 0.28125 0.1419786
Add(String, SqlDbType, Int32) Yes Prepared 0.070312 0.1399672


-- 101_Application_Summary





SSMS 檢視資料表的資料類型、長度

-- 111_SSMS_Table_Data_Type_Size







範例程式碼

20171114_Avoid Plan Cache Pollution

https://drive.google.com/drive/folders/1h_f32oYgR8ui53hMSz9tUUsd1iql-IdD?usp=sharing





參考資料

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/

SqlParameterCollection.Add Method
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.add.aspx

SqlParameterCollection.AddWithValue Method (String, Object)
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue(v=vs.110).aspx

Plan Caching and Recompilation in SQL Server 2012
https://msdn.microsoft.com/en-us/library/dn148262.aspx

Simple Parameterization
https://technet.microsoft.com/en-us/library/ms186219(v=sql.105).aspx

[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

[SQL Server] Plan Cache Pollution - Hard-Coded SQL
http://sharedderrick.blogspot.tw/2017/11/sql-server-plan-cache-pollution-hard.html

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

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

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/

2017-11-04

[SQL Server] View or Change Compatibility Level of Databases, 相容性層級


若要發揮新版本 SQL Server 的功能,調整 資料庫相容性層級 到 目前最新版本 的相容性層級。

相容性層級只會提供與舊版 SQL Server 之間的部分回溯相容性。相容性層級只會影響指定之資料庫的行為,而不會影響整個伺服器的行為。

若要發揮新版本 SQL Server 功能,調整 資料庫相容性層級 到 目前最新版本 的相容性層級。

-- 20_Version_Of_SQL_Server_COMPATIBILITY_LEVEL





變更全部資料庫的相容性層級 到 最新版本

功能:
  1. 偵測 目前最新版本 的資料庫相容性層級。
  2. 變更全部資料庫的相容性層級 到 最新版本。不包含系統資料庫。

-- 01 - View all databases compatibility level
USE master
GO
SELECT name 'DB', compatibility_level FROM sys.databases
GO

-- 02 - Change all databases compatibility level
USE master
DECLARE @cl tinyint, @tsql nvarchar(4000) 
SELECT @cl=compatibility_level FROM sys.databases WHERE name='master'

SET @tsql = (
 SELECT 'ALTER DATABASE ' + name +' SET COMPATIBILITY_LEVEL = ' + CAST(@cl AS varchar(10)) + '; '
 FROM sys.databases
 WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
 FOR XML PATH('') )

EXEC sp_executesql @tsql;
GO

-- 03 - View all databases compatibility level
USE master
GO
SELECT name 'DB', compatibility_level FROM sys.databases
GO


補充:使用 sp_MSforeachdb

-- 02 - Change all user databases compatibility level - sp_MSforeachdb
USE master
DECLARE @cl tinyint, @tsql varchar(4000) 
SELECT @cl=compatibility_level FROM sys.databases WHERE name='master'

SET @tsql = '
 IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
  BEGIN 
   ALTER DATABASE ? SET COMPATIBILITY_LEVEL = ' + CAST(@cl AS varchar(10)) + 
  ' END';

EXEC sp_MSforeachdb @tsql;
GO




-- 01_View all databases compatibility level


-- 02_資料庫相容性層級


-- 03_資料庫相容性層級


-- 04_ Change all databases compatibility level

功能:
  1. 偵測 目前最新版本 的資料庫相容性層級
  2. 變更全部資料庫的相容性層級 到 最新版本。不包含系統資料庫。


-- 補充:Change all user databases compatibility level - sp_MSforeachdb



-- 05_View all databases compatibility level


-- 10_資料庫相容性層級_已調整為140







變更 資料庫的相容性層級

在使用者連接到資料庫時變更相容性層級,可能會讓使用中的查詢產生不正確的結果集。
例如,如果在編譯查詢計劃時變更相容性層級,編譯的計畫可能會同時以新的和舊的相容性層級為根據,而導致不正確的計畫以及可能不精確的結果。

此外,如果此計畫放入計畫快取且重複用於後續的查詢,問題可能更嚴重。 若要避免發生不精確的查詢結果,建議您使用下列程序變更資料庫的相容性層級:

  1. 使用 ALTER DATABASE SET SINGLE_USER 將資料庫設定為單一使用者存取模式。
  2. 變更資料庫的相容性層級。
  3. 使用 ALTER DATABASE SET MULTI_USER 將資料庫設定成多使用者存取模式。
  4. 如需有關設定資料庫的存取模式的詳細資訊,請參閱ALTER DATABASE (TRANSACT-SQL ).


範例程式碼

20171104_Change_Compatibility_Level
https://drive.google.com/drive/folders/1t1UbOLbQqFSylvFb8ZQRB5TTsGbisMfc?usp=sharing




ALTER DATABASE (TRANSACT-SQL) 相容性層級
https://docs.microsoft.com/zh-tw/sql/t-sql/statements/alter-database-transact-sql-compatibility-level

SQL Server Version List, Service Pack, Cumulative Updates - 版本清單, 累積更新 - updated to SQL Server 2017
http://sharedderrick.blogspot.tw/2017/10/sql-server-version-list-service-pack.html