[SQL SERVER][Memo]了解參數探測行為(1)
我想大家都知道重用執行計畫所帶來的好處(減少記憶體壓力和CPU資源)，
因為過多的編譯次數同時也帶來過多的效能資源消耗 。
但某些情況下重用執行計畫反而會帶來一些壞處(查詢效能低落問題)，
而當你了解什麼是參數探測行為後，你就會知道為什麼我會這麼說了。
什麼是參數探測行為
最佳化工具會在重新啟動 MicrosoftSQL Server 後、首次執行預存程序時自動發生，
這時會將有使用的參數值一同包含在查詢計畫的一部份中，
這樣的作法對大多情況下來說是比較好的，只要SP後續執行都是使用常值，
那基本上都能夠重用執行計畫，但有可能會誤用執行計畫(造成查詢效能成本過高)。
換句話說，假設第一次返回資料量相當龐大(已快取執行計畫)，
執行第二次返回資料量很小但查詢成本(效能)反而不如預期(因為使用了第一次執行計畫)，
這樣就是標準的參數探測行為。
測試參數探測行為
--建立 Store Procedure
create proc dbo.getCustomer(@CustType nchar(1))
as
select CustomerID,AccountNumber,ModifiedDate
from Sales.Customer
where CustomerType = @CustType
--建立 Index
create nonclustered index idx_1 on Sales.Customer(CustomerType)
include(AccountNumber,ModifiedDate)
--確認資料筆數
select CustomerType,COUNT(1) as 'count'
from Sales.Customer
group by CustomerType
order by COUNT(1)
參數 S=>701 , I=>18484。
執行第一次 SP(返回大量資料)
exec dbo.getCustomer @CustType='I'
實際資料列數目和估計的資料列數目都為 18484。
整體查詢成本:0.077534。
由於該SP執行計畫已經快取，我接下來再執行第二次 SP(返回少量資料)。
exec dbo.getCustomer @CustType='S'
實際資料列數目和估計的資料列數目不同(差距相當大)。
整體查詢成本:0.077534。
這裡因為參數探測行為導致誤用了執行計畫(因為快取了第一次執行計畫)，
下面我會先刪除執行計畫快取並再次執行SP，而你將看到執行計畫的改變。
刪除執行計畫快取並執行SP(返回少量資料)
--請勿在正式環境執行
dbcc freeproccache
exec dbo.getCustomer @CustType='S'
實際資料列數目和估計的資料列數目都為 701。
成本:0.0053853。
你可以看到最後整體的查詢成本由0.077534降低為0.0053853。
結論:
大多情況下重用執行計畫可以為效能帶來不少好處，
但某些情況下反而造成效能低落，而當我在處理查詢效能調效相關問題時，
我只能坦白的說，很多事情真的無法面面俱到...
而下一篇我將繼續討論有那些方法可以避免參數探測行為。
參考
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
with recompile