日前檢視一支StoreProcedure時無意間發現了這一個問題。當時該SP的開發者在該SP中利用User丟進來的參數來串成他要的Query字串後再透過sp_executesql執行該語法字串。等於是在SP中組出一個Adhoc字串來跑，這樣完全無法享受到重用執行計畫的好處。
以下簡單模擬一下該狀況。
首先我新建一個Database及一張資料表tb，並在表中塞入大量資料，如下圖所示。
接下來我建立一個StoreProcedure去抓取tb資料表中資料，如下圖所示。請注意該SP中我另外將參數 @id 加入一個字串 @strSQL 後再透過sp_executesql去執行 @strSQL 的內容。
我們先清空記憶體中所有的執行計畫。
接下來執行我們建立的預存程序 usp_GetName 並傳入參數 @id 為2000，下圖中可以看見SQL回傳正確的值，且有編譯執行計畫的耗時(如下圖紅色圈選處)。
接下來我們檢視SQL記憶體中因上步驟的查詢而產出了哪一些執行計畫。如下圖所示我們除了看見SQL因簡單參數化而產出的Prepared類型執行計畫外，還有一個Adhoc的執行計畫(如下圖紅色圈選處)。
我們再一次執行該Procedure，這一次參數帶入2001。
再次檢視一下記憶體中的執行計畫狀態，發現又多了一筆Adhoc的執行計畫(如下圖紅色圈選處)。而Prepared執行計畫的使用次數變成了2次，此時你會說那SQL還是有重用執行計畫啊。針對於這種簡單的語法SQL會產出Prepared的執行計畫來重用，一但您的語法是極為複雜時，SQL就不會自動產出Prepared執行計畫。(SqlServer並不是所有的sql語句都進行參數化處理，只有對那些有且只有一種執行計劃的sql語句，才自動參數化處理)
接下來我們改寫該Procedure。如下圖，我們完全不用串字串方式來做，這樣也可以避免掉SQL Injection的問題，使用sp_executesql執行語法時我們也傳入參數值 @id。
一樣先清空記憶體。
再次執行Procedure，傳入參數值2000。
只產出一個Prepared執行計畫物件，並無Adhoc型態的物件。
再次執行Procedure，傳入參數值2001。
剛剛產出的Prepared執行計畫物件使用次數變成2次，並無Adhoc型態的物件。
我是ROCK
rockchang@mails.fju.edu.tw
