德瑞克：SQL Server 學習筆記: [Performance Tuning] Use SqlParameter, specified SqlDbType and length

使用 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