[SQL SERVER][Performance]淺談簡單和強制參數化
簡單參數化:
(擷取線上叢書)
不多說，馬上來驗證看看。
select * from dbo.TB_MAIL_LIST
where [owner]='董事長室'
go
select * from dbo.TB_MAIL_LIST
where [owner]='品質管理處'
go
--查看相關執行計畫資訊
select qp.usecounts as '使用次數',qp.cacheobjtype as '快取類型', objtype as '物件類型',st.text
from sys.dm_exec_cached_plans qp
cross apply sys.dm_exec_sql_text(plan_handle) st
where st.text not like '%sys%'
and st.text like '%TB_MAIL_LIST%'
第一次查詢
第二次查詢
可以看到SQL Server將原本查詢陳述句轉換如下
(@1 varchar(8000))SELECT * FROM [dbo].[TB_MAIL_LIST] WHERE [owner]=@1
常值的部分使用參數取代，參數資料類型為varchar(8000)，
意指，只要常值長度不超過8000且又是varchar類型，那都可以重複使用該參數化執行計畫，
這樣的過程就稱為簡易參數化，但某些情況下，簡易參數化處理可能會幫到忙，
下面我在測試一個例子，你將明白為什麼我會這麼說。
select * from dbo.TB_MAIL_LIST
where list_id=1
go
select * from dbo.TB_MAIL_LIST
where list_id=9987
go
--查看相關執行計畫資訊
select qp.usecounts as '使用次數',qp.cacheobjtype as '快取類型', objtype as '物件類型',st.text
from sys.dm_exec_cached_plans qp
cross apply sys.dm_exec_sql_text(plan_handle) st
where st.text not like '%sys%'
and st.text like '%TB_MAIL_LIST%'
第一次查詢
第二次查詢
可以看到這裡產生了兩個參數化執行計畫，簡易參數化針對常值 9987 自動設定該參數資料類型為 smallint(-32768~32767)，
針對常值 1 自動設定該參數資料類型為 tinyint(0~255)，如果你的SQL Server有記憶體不足的壓力，
相信這不是你想看到的情況，接下來我們來看看設定強制參數化又會有什麼樣的改變。
強制參數化:
(擷取線上叢書)
簡單參數化讓SQL Server自動選擇較安全的參數資料類型，藉以提高執行計畫的重用率，
強制參數化在查詢編譯期間會將常值轉換為參數，藉以減少發生編譯和重新編譯的頻率
(有時可改善特定資料庫的效能，但也有些例外)，
這裡我要強調一點，在實務上我個人認為沒必要去設定強制參數化，
因為這可能會導致選到次佳的執行計畫...等(可以參考使用強制參數化指導方針有更詳細的說明)，
但不可否認在某些特定情況下，強制參數化確實能夠改善資料庫效能。
設定資料庫為強制參數化
alter database ricotest1 set parameterization forced;
select * from dbo.TB_MAIL_LIST
where list_id=1
go
select * from dbo.TB_MAIL_LIST
where list_id=9987
go
--查看相關執行計畫資訊
select qp.usecounts as '使用次數',qp.cacheobjtype as '快取類型', objtype as '物件類型',st.text
from sys.dm_exec_cached_plans qp
cross apply sys.dm_exec_sql_text(plan_handle) st
where st.text not like '%sys%'
and st.text like '%TB_MAIL_LIST%'
第一次查詢
第二次查詢
當資料庫設定為強制參數化後，這裡只產生一個參數化執行計畫，
該參數類型為 int ，這表示，
只要常值類型為 int 且範圍在 –2147483648 ~ 2147483647都可以重用該參數化執行計畫，
看上去感覺強制參數化好像很不賴，但強制參數化針對常值部分也有諸多例外，如下圖:
(擷取線上叢書)
結論:
在你了解簡單和強制參數化是在做什麼後，你可能會問，什麼狀況該用簡單或強制參數化呢?
你可以參考我之前所發表[SQL SERVER][Memo]撰寫Stored Procedure小細節 一文中有提到，
請使用sp_executesql取代Execute(Exec) 陳述式..等。
使用 sp_executesql 建立的 Transact-SQL 陳述句會和先前執行之陳述句的執行計畫進行比對，
來減輕因編譯新執行計畫而造成的額外負擔，並提高重用率，下面我在舉個例子，
你將了解sp_executesql 所帶來的好處和方便。
--更改資料庫為簡單參數化
alter database ricotest1 set parameterization simple;
declare @mystatement nvarchar(max);
set @mystatement='select * from dbo.TB_MAIL_LIST where list_id=@myvalue';
--直接明確指定該參數類型
execute sp_executesql @mystatement,N'@myvalue int', @myvalue = 1;
execute sp_executesql @mystatement,N'@myvalue int', @myvalue = 9987;
go
--查看相關執行計畫資訊
select qp.usecounts as '使用次數',qp.cacheobjtype as '快取類型', objtype as '物件類型',st.text
from sys.dm_exec_cached_plans qp
cross apply sys.dm_exec_sql_text(plan_handle) st
where st.text not like '%sys%'
and st.text like '%TB_MAIL_LIST%'
參考