日前在Tunning一句語法時，在SSMS的視窗下撰寫TSQL測試。當時發現我用變數當參數來查跟直接用數字來當參數查時會跑不同的執行計畫，Code的範例如下
--參數查詢
Declare @i int=1;
Select * From tb Where id=@i;
GO
--直接數值查詢
Select * From tb Where id=1;
GO
當時不管怎麼調或統計更新或加索引，使用Declare變數來查就是慢就是跑錯誤的執行計畫，狀況跟Parameter Sniffing不一樣(更新統計無效)。
以下簡易做個實驗來展示一下何謂Parameter Sniffing。
首先建立一個Table叫tb1，然後針對id2欄位建立一個非叢集索引ix2。
我們大量寫入id=1的資料，而只寫入1筆id=2的資料到tb1裡。
下圖我們清除Cache後，再一次更新tb1的統計資訊。
建立一個預存程序sp1，語法是很單純的一個查詢。
下圖中我們執行sp1並傳入參數@i數值給1。由於筆數多，所以SQL的執行計畫採Scan模式。
接下來執行sp1並傳入參數@i數值給2。但是該筆數只有1筆，但SQL還是根據剛剛數值1的統計資訊所建立的執行計畫來執行。所以還是採用Scan模式，而這是一個效能較差的查詢模式。
以上簡易的Demo了何謂Parameter Sniffing。
為了解決Parameter Sniffing的其中一個方式就是在預存程序內宣告一個Local變數，並將傳入SP的參數指給Local變數再查詢(如下圖所示)，這樣SQL就會根據資料表筆數建立一個通用型的執行計畫。然而這不是最佳解，但有時候的確可以解決Parameter Sniffing問題，但絕不是跑最佳執行計畫。
我們先清掉Cache後更新統計再執行 上一步驟我們建立的sp2，同樣執行傳入參數@i數值給1。此時SQL一樣跑Table Scan，但在預估筆數時可以看到SQL預估筆數是16000多筆，並無實際去統計id2=1的筆數有多少。
我們再一次清掉Cache後更新統計再執行sp2，這次傳入參數@i數值給2。此時SQL還是跑Table Scan，預估筆數時可以看到SQL預估筆數還是16000多筆，沒有實際去統計id2=2的筆數有多少。
接下來我再次增加tb1到26萬筆資料，看看sp2會估計多少。如下圖所示當我將資料增加到26萬筆後再執行sp2傳入參數@i數值給2，這次SQL估計筆數是13萬筆左右。所以感覺SQL是取總筆數的一半來當參考值建立執行計畫，但實際是否是這樣得需要更深入研究。
看完上述簡易Demo後大概可以知道我當時用變數當參數來查跟直接用數字來當參數查時為何會跑不同的執行計畫了。因為即便我們寫法如下圖所示，SQL還是不會去先去看變數@i內容是多少然後再建立執行計畫，他還是根據自己概估的數字來建立執行計畫。
要想讓SQL先去檢查變數@i內容是多少然後根據統計後再建立執行計畫的話，請在查詢語法後加入Option(Recompile)。
上圖中我們在語法加入Option(Recompile)後會讓SQL在執行此句語法時即時去檢視參數值並根據統計資料再建立執行計畫。
所以上圖中SQL改用Table Seek方式來搜尋資料。
下圖中為官方文件對Recompile的說明，以上感謝Colin Lin老師的提點。
我是ROCK
rockchang@mails.fju.edu.tw
