RiCo技術農場

昨天朋友說參考我之前所寫的一篇文章來避免參數探測問題，

他選用第一種方法(with recompile)雖然有達到避開參數探測問題，

但是with recompile卻是重新編譯整各SP，導致執行該SP速度變慢，

他問我說，SQL2005後應該有支援部分編譯，不會編譯整個SP，

但怎麼還是重新編譯整各SP呢?

我回說如果只要編譯部分的話就要使用第三種方法option(recompile)，

下面我簡單重現該問題。

 

SP內容如下

create proc usp_Big(@CustType nchar(1))
as

select CustomerID,AccountNumber,ModifiedDate 
from Sales.Customer
where CustomerType = @CustType --存在參數探測問題

select apLogid,logdate,logfile 
from ap_log
where apLogid>=11 and apLogid <=1111  

declare @startdate datetime,@enddate datetime
set @startdate='20010708'
set @enddate='20021231' 
exec user_GetCustomerShipDates @startdate ,@enddate

 

go

上面SP其實只有第一部份陳述式存在參數探測問題，

而其他部分我希望SQL SERVER能夠快取其執行計畫，

雖然透過with recompile可以避開該問題，

但SP若有其它陳述式內容複雜龐大的話，

將導致執行該SP速度變慢(大部分時間都浪費在重新編譯執行計畫上)。

先用with recompile測試

exec usp_Big 'A' 

with recompile 

利用 DMV 查看recompile狀況

SELECT 
qs.plan_generation_num as '重新編譯計畫序號',
qs.execution_count as '執行次數',
object_name(st.objectid) as '物件名稱',
qp.cacheobjtype as '快取類型',
qp.objtype as '物件類型',
st.TEXT
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) AS st
cross apply sys.dm_exec_cached_plans qp
where st.dbid=11 
group by qs.plan_generation_num,qs.execution_count,object_name(st.objectid)
,qp.cacheobjtype ,st.TEXT,qp.objtype 
ORDER BY plan_generation_num DESC

重複執行三次

可以看到記憶體的計畫快取區中，

只有user_GetCustomerShipDates  SP的執行計畫被快取

(因為user_GetCustomerShipDates 沒有with recompile這是正常的)。

profiler 擷取看的更清楚

紅色框說明每句陳述式都是經過完整編譯，

唯有 user_GetCustomerShipDates SP被快取。

note:eventclass=sp:cachehit 說明在快取區中找到該執行計畫。

 

修改SP改用option(recompile)測試

只針對存在參數探測問題陳述式進行recompile，其他部分不需要進行recompile。

第一次執行(不使用with recompile)

exec usp_Big 'A' 

利用 DMV 查看recompile狀況

紅色框框處編號為2(不同於其他編號1)，

說明為新建立執行計畫(只針對 select CustomerID..陳述式進行重新編譯)。

利用profiler

可以看到eventclass:SP&SQL:recompile(select CustomerID..) ，

下面重複執行2次會更清楚。

重複執行2次

利用 DMV 查看recompile狀況

紅色框編號變為4(執行次數永遠都為1)，綠色框編號沒變，且執行次數持續累加(重用執行計畫)。

profiler

可以看到觸發eventclass=sp:cachehit(usp_big)。

 

 

 

 

參考

sp_recompile (Transact-SQL)

查詢提示 (Transact-SQL)

重新編譯預存程序

SP:CacheHit 事件類別