[SQL SERVER][Memo]了解參數探測行為(2)
上一篇我大概談了什麼是參數探測行為，
而這篇我將討論有那些方法可以避免參數探測行為。
1.執行 SP 並使用With Recompile
exec dbo.getCustomer @CustType='S' with recompile
2.使用本機變數取代參數(建立通用查询的執行計畫)
create proc dbo.getCustomer(@CustType nchar(1))
as
declare @loccusttype nchar(1)
set @loccusttype=@CustType;
select CustomerID,AccountNumber,ModifiedDate
from Sales.Customer
where CustomerType = @loccusttype
執行第一次
exec dbo.getCustomer @CustType='I'
執行第二次
exec dbo.getCustomer @CustType='S'
你可以看到執行兩次的執行計畫整體查詢成本都相同。
如果你的SP相當龐大複雜，並且不想透過強制編譯執行計畫來避免參數探測行為的話，
那你可以使用本機變數來取代參數(可能需改寫SP)，
這樣查詢最佳化工具會依參數來建立一個通用的執行計畫。
估計的資料列數目:
select 1.0*count(*)/count(distinct CustomerType)
from Sales.Customer
3.使用查詢提示(recompile)
create proc dbo.getCustomer1(@CustType nchar(1))
as
select CustomerID,AccountNumber,ModifiedDate
from Sales.Customer
where CustomerType = @CustType
option(recompile)
4.使用查詢提示(OPTIMIZE FOR, OPTIMIZE FOR UNKNOWN )
ps: OPTIMIZE FOR UNKNOWN 是SQL2008後才有的 hint。
create proc dbo.getCustomer1(@CustType nchar(1))
as
select CustomerID,AccountNumber,ModifiedDate
from Sales.Customer
where CustomerType = @CustType
option(OPTIMIZE FOR (@CustType = 'S'))
以上的4種方法都可以避免參數探測行為，
請大家自行對症下藥服用即可。
參考
查詢提示 (Transact-SQL)
