2017-11-30

[SQL Server]: Query Replication Configuration - Get Publication, Article and PublicationType information


延續前一篇:[SQL Server]: Query Replication Configuration - Analysis Publication and Article for each Distributor

Get Publication, Article and PublicationType information


  • 查詢 PublisherDB, SubscriberDB, Publication,  Article, Distributor, Distribution, PublicationType 等相關資訊。
  • 若 Distributor 有多個 Distribution Database,此 Script 可完整取得各個 Distribution Database 的相關資訊。


-- 113_Get Publication, Article and PublicationType information



連線到 Distributor,執行以下 T-SQL Script:


-- EX3. Get Publication, Article and PublicationType information
-- PublisherDB, SubscriberDB, Publication,  Article, Distributor, Distribution, PublicationType
-- Connect to Distributor, for example: Dist01
USE master
DECLARE @tsql varchar(4000) 

SET @tsql = 'IF ''?'' IN((SELECT name FROM sys.databases WHERE is_distributor=1)) 
BEGIN
 USE [?]
 SELECT sc.publisher_db PublisherDB, sc.subscriber_db SubscriberDB, pu.publication Publication,
 ar.source_owner [Schema] ,ar.article Article,
 @@SERVERNAME Distributor, DB_NAME() DistributorDB, 
 CASE publication_type 
  WHEN 0 THEN ''Transactional''
  WHEN 1 THEN ''Snapshot''
  WHEN 2 THEN ''Merge''
 END PublicationType
FROM MSsubscriptions sc INNER JOIN MSarticles ar ON sc.article_id = ar.article_id
 INNER JOIN MSpublications pu ON pu.publication_id = sc.publication_id
ORDER BY pu.publication
END';

EXEC sp_MSforeachdb @tsql;
GO



範例程式碼

20171129_Query Replication Configuration
https://drive.google.com/drive/folders/1H2hyxRDlfuMzdgJmnSAwlg6P6MsuN_3O?usp=sharing





參考資料

[SQL Server]: Query Replication Configuration - Analysis Publication and Article for each Distributor
http://sharedderrick.blogspot.tw/2017/11/sql-server-query-replication_30.html

[SQL Server]: Query Replication Configuration - Get completed setup replication information
http://sharedderrick.blogspot.tw/2017/11/sql-server-query-replication.html

MSpublications (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mspublications-transact-sql

MSsubscriptions (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mssubscriptions-transact-sql

[SQL Server]: Query Replication Configuration - Analysis Publication and Article for each Distributor


延續前一篇:[SQL Server]: Query Replication Configuration - Get completed setup replication information


Analysis Publication and Article for each Distributor


  • 分析彙總 Publication 與 Article 的相關資訊。
  • 若 Distributor 有多個 Distribution Database,此 Script 可完整取得各個 Distribution Database 的相關資訊。


-- 112_Analysis Publication and Article for each Distributor



連線到 Distributor,執行以下 T-SQL Script:

-- EX2. Analysis Publication and Article for each Distributor
-- Connect to Distributor, for example: Dist01
USE master
DECLARE @tsql varchar(4000) 

SET @tsql = 'IF ''?'' IN((SELECT name FROM sys.databases WHERE is_distributor=1)) 
BEGIN
 USE [?]
 SELECT @@SERVERNAME Distributor, DB_NAME() Distribution,
 pu.publication Publication, COUNT(*) ObjectCount
 FROM MSarticles ar INNER JOIN MSpublications pu ON pu.publication_id = ar.publication_id
 GROUP BY pu.publication
END';

EXEC sp_MSforeachdb @tsql;
GO




範例程式碼

20171129_Query Replication Configuration
https://drive.google.com/drive/folders/1H2hyxRDlfuMzdgJmnSAwlg6P6MsuN_3O?usp=sharing





參考資料

[SQL Server]: Query Replication Configuration - Get completed setup replication information
http://sharedderrick.blogspot.tw/2017/11/sql-server-query-replication.html

MSpublications (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mspublications-transact-sql

MSsubscriptions (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mssubscriptions-transact-sql

[SQL Server]: Query Replication Configuration - Get completed setup replication information


在擁有多組 SQL Server Replication 的環境,使用 T-SQL Script 方式來查詢相關的組態資訊,會是更有效率的方式。


連線到 Distributor,由 Distribution 取得 Replication 相關的組態資訊,這是 Lightweight(輕量級) 且 無須使用 Publisher, Subscriber 的系統資源。



Get completed setup replication information

連線到 Distributor,執行以下 T-SQL Script:

若 Distributor 有多個 Distribution Database,此 Script 可完整取得各個 Distribution Database 的相關資訊。

查詢 Replication 的組態資訊,包含:
Publisher, Publisher Database, Publication, Subscriber, Subscriber Database, Distributor and  Distribution Database, etc.

-- 111_Get completed setup replication information



-- EX1. Get completed setup replication information:
-- Publisher, PublisherDB, Publication, Subscriber, SubscriberDB, Distributor, Distribution
-- Connect to Distributor, for example: Dist01
USE master
DECLARE @tsql varchar(4000) 

SET @tsql = 'IF ''?'' IN((SELECT name FROM sys.databases WHERE is_distributor=1)) 
BEGIN
 USE [?]
 SELECT DISTINCT srv.name Publisher, p.publisher_db PublisherDB, p.publication Publication, 
  ss.name Subscriber, s.subscriber_db SubscriberDB, 
  @@SERVERNAME Distributor, DB_NAME() [Distribution Database]
 FROM MSpublications p INNER JOIN MSsubscriptions s 
  ON p.publication_id = s.publication_id 
  INNER JOIN sys.servers ss ON s.subscriber_id = ss.server_id 
  INNER JOIN sys.servers srv ON srv.server_id = p.publisher_id 
 ORDER BY 1,2,3
END';

EXEC sp_MSforeachdb @tsql;
GO



Review Replication Configuration on the Distributor

-- 121_View_Distributor_Distribution




-- 121_View_Distributor_Publisher





範例程式碼

20171129_Query Replication Configuration
https://drive.google.com/drive/folders/1H2hyxRDlfuMzdgJmnSAwlg6P6MsuN_3O?usp=sharing




參考資料

MSpublications (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mspublications-transact-sql

MSsubscriptions (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mssubscriptions-transact-sql

2017-11-23

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE: Clear procedure cache for a particular database - 清除特定資料庫的 Plan Cache


若要清除 特定資料庫的 Plan Cache,在 SQL Server 2016 可以使用 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

執行此陳述式將會清除 目前資料庫中的 Plan Cache,這表示屬於該資料庫的 所有查詢 都將會需要重新編譯。






ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE: Clear procedure cache for a particular database - 清除特定資料庫的 Plan Cache


01. 在資料庫 Northwind,執行 4 段 SQL 查詢。

-- 011_Ad hoc Query



02. 檢視 Plan Cache

  • 確認有屬於資料庫 Northwind 的 Plan Cache


-- 012_ View Each cached query and execution plan



03. 檢視 Plan Cache
  • 確認有屬於資料庫 Northwind 的 Plan Cache
-- 013_View cached query for each database



04. 執行 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

  • 清除資料庫 Northwind 的 Plan Cache


-- 021_Clears the procedure (plan) cache for a particular database



05. 檢視 Plan Cache
  • 確認 沒有 資料庫 Northwind 的 Plan Cache

-- 022_ View Each cached query and execution plan



06. 檢視 Plan Cache
  • 確認 沒有 資料庫 Northwind 的 Plan Cache
-- 023_View cached query for each database






3-part name queries 不受影響

若是為 3 部分名稱(3-part name queries, DB.Schema.Object) 的查詢,ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 採用確認 目前資料庫 為何者的方式來處理。

舉例來說:

  • 在資料庫 master 中,執行 3-part name queries,橫跨到資料庫 Northwind 存取物件,但其 目前資料庫是 master。
  • 若對資料庫 Northwind 執行 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE,將清除目前資料庫是 Northwind 的 Plan Cache,而先前在資料庫 master 中的 3-part name queries,則不受到影響。


-- 031_3-part name queries(DB.Schema.Object)



-- 032_All_in_master_db





範例程式碼

20171123_DB_CLEAR PROCEDURE_CACHE
https://drive.google.com/drive/folders/1o8J4Xsxo1UC54PG8Mfu0e-kxGir0CC2A?usp=sharing




參考資料

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

DBCC FREEPROCCACHE - Remove the specific plan from the cache - 由 Plan Cache 中移除指定的 Execution Plan
http://sharedderrick.blogspot.tw/2017/11/dbcc-freeproccache-remove-specific-plan.html

2017-11-22

DBCC FREEPROCCACHE - Remove the specific plan from the cache - 由 Plan Cache 中移除指定的 Execution Plan


DBCC FREEPROCCACHE 用於移除 Plan Cache 的項目,可用的方式有:
  1. 移除全部的 cache
  2. 移除指定的 plan_handle
  3. 移除指定的 sql_handle
  4. 移除指定的 pool_name - Resource Governor resource pool


副作用:
  • 移除 Plan Cache 後,因無可重複使用的 Execution Plan,導致要重新編譯 Execution Plan,耗費系統資源。

-- Remove the specific plan from the cache - 由 Plan Cache 中移除指定的 Execution Plan






DBCC FREEPROCCACHE - Remove the specific plan from the cache - 由 Plan Cache 中移除指定的 Execution Plan


01. 在資料庫 Northwind,執行 4 段 SQL 查詢。


-- 021_Run Ad hoc Query



02. 若要移除 編號 8 的 Execution Plan:

  • Obj Type: Adhoc
  • T-SQL: SELECT * FROM Northwind.dbo.Products WHERE ProductID =2
  • plan_handle: 0x06000100F8BDCD2780E2D1F3D001000001000000000000000000000000000000000000000000000000000000


-- 022_View_Plan_Cache



03. 執行 DBCC FREEPROCCACHE,並加入指定的 plan_handle。

  • 就可以由 Plan Cache 中移除指定的 Execution Plan


-- 031_DBCC FREEPROCCACHE - Remove the specific plan from the cache


04. 觀察: Plan Cache:

  • 確認已經移除 編號 8 的 Execution Plan.

05. 若要移除 編號 1 的 Execution Plan:

  • Obj Type: Prepared
  • (@1 tinyint)SELECT * FROM [Northwind].[dbo].[Products] WHERE [ProductID]=@1
  • plan_handle: 0x0600010072761B2DE0E8D1F3D001000001000000000000000000000000000000000000000000000000000000

-- 032_View_Plan_Cache



06. 執行 DBCC FREEPROCCACHE,並加入指定的 plan_handle。

  • 就可以由 Plan Cache 中移除指定的 Execution Plan

-- 041_DBCC FREEPROCCACHE - Remove the specific plan from the cache


04. 觀察: Plan Cache:

  • 確認已經移除指定的 Execution Plan.

-- 042_View_Plan_Cache






DBCC FREEPROCCACHE - Clearing all plans from the plan cache - 清除全部的 Plan Cache

01. 觀察 Plan Cache

-- 001_Plan_Cache



02. 執行 DBCC FREEPROCCACHE,移除全部的 Plan Cache

-- 011_DBCC FREEPROCCACHE_All



03. 若不想看到 DBCC FREEPROCCACHE 執行回傳結果,可以搭配參數:

  • WITH NO_INFOMSGS


-- 012_DBCC FREEPROCCACHE_NO_INFOMSGS



04. 觀察 Plan Cache: 已移除全部的 Plan Cache

-- 002_Plan_Cache_Clean






範例程式碼

20171122_DBCC FREEPROCCACHE
https://drive.google.com/drive/folders/1LarqPyGXq6Istw-eResJOlvvHo_TyHLC?usp=sharing






移除 Plan Cache

移除 Plan Cache 的方式有:

  1. DBCC FREEPROCCACHE 
  2. access check cache bucket count
  3. access check cache quota
  4. clr enabled
  5. cost threshold for parallelism
  6. cross db ownership chaining
  7. index create memory
  8. max degree of parallelism
  9. max server memory
  10. max text repl size
  11. max worker threads
  12. min memory per query
  13. min server memory
  14. query governor cost limit
  15. query wait
  16. remote query timeout
  17. user options
  18. ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE - SQL Server 2016




注意事項


  • DBCC FREEPROCCACHE 不會清除原生編譯預存程序(供 memory-optimized tables 使用)的執行統計資料。 
  • 程序快取沒有包含原生編譯預存程序的相關資訊。




參考資料

Eight Different Ways to Clear the SQL Server Plan Cache
https://www.sqlskills.com/blogs/glenn/eight-different-ways-to-clear-the-sql-server-plan-cache/

SQLskills 101: The Other Bad Thing About Clearing Procedure Cache
https://www.sqlskills.com/blogs/erin/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache/

DBCC FREEPROCCACHE
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql

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