德瑞克：SQL Server 學習筆記: 2017/11

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 的項目，可用的方式有：

移除全部的 cache
移除指定的 plan_handle
移除指定的 sql_handle
移除指定的 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 的方式有：

DBCC FREEPROCCACHE
access check cache bucket count
access check cache quota
clr enabled
cost threshold for parallelism
cross db ownership chaining
index create memory
max degree of parallelism
max server memory
max text repl size
max worker threads
min memory per query
min server memory
query governor cost limit
query wait
remote query timeout
user options
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) ，請記得指定資料長度，可以提升效能：

以效能角度來分析，明確指定資料類型、資料長度，.NET 程式無須額外判斷，節省資源。
讓 SQL Server 能使用 Parameterization(參數化)，重複使用 Execution Plan 的作法，有效使用系統資源。
避免發生 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

延續前一篇 [SQL Server] Plan Cache Pollution - Hard-Coded SQL

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 是 Adhoc，1,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 是 Prepared，4 份約耗費 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 Value	Parameter Data Types	Use Counts	Obj Type
1~1001	@ctn1 nvarchar(25)	1001	Prepared

分析

執行 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) ，請記得指定資料長度，可以提升效能：

以效能角度來分析，明確指定資料類型、資料長度，.NET 無須額外判斷，節省資源。
讓 SQL Server 能使用 Parameterization(參數化)，重複使用 Execution Plan 的作法，有效使用系統資源。
避免發生 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

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