前一段時間，給一位朋友公司做咨詢，看到他們的很多的存儲過程都存在動態sql語句執行，sp_executesql，即使在沒有動態表名，動態字段名的情況下仍然使用sp_executesql,這個做法是不太明智的，會存在一些性能方面的問題。
先說說什么場景使用這個系統存儲過程吧，sp_executesql,是sql server動態執行一段可以帶有參數（內參，外參）的語句文本的系統存儲過程，傳入sp_executesql 的參數會以參數的形式傳遞，不會是以拼湊sql的形式傳遞，所以能夠在不得不拼接sql語句的情景下使用以防止sql注入。不得不拼接sql的情景包括 傳遞in內參數，動態決定表列，列名，還有就是like，為防止sql注入，也不得不拼接sql。按理來說這是一個非常好的存儲過程，但是，由于他本身的限制，會對查詢性能有很大的影響，下面我舉個例子。

使用northwind數據庫，
執行：
select * from orders where customerid = 'SAVEA';

執行：
select * from orders where customerid = 'CENTC';

這兩個語句的唯一不同就是客戶號不一樣，一個在訂單表內有31個重復值，一個沒有重復值。
然后咱們再來對比當這個語句放在了一個動態執行的sql語句內部的情況如何。


創建如下存儲過程：

Code
USE [Northwind]
GO
/****** 對象:  StoredProcedure [dbo].[testexecutesql]    腳本日期: 10/15/2009 20:09:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[testexecutesql](@customerid nchar(5))
as
begin

exec sp_executesql N'select * from orders where customerid = @cid ',
N'@cid as nchar(5)',@cid = @customerid   ;
end

然后執行這個存儲過程：
exec testexecutesql 'SAVEA';
其執行計劃如下圖，是個聚集索引掃描：

(31 行受影響)
表 'Orders'。掃描計數 1，邏輯讀取 22 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

使用聚集索引掃描是個很明智的選擇，咱們可以來看看customerid上的非聚集索引的統計信息，orders表共830行，其中客戶'SAVEA'就有31個訂單，所以優化器選擇使用聚集索引掃描而不是嵌套循環的book mark look up。
然后咱們再來執行一個：
exec testexecutesql 'CENTC';
區別僅僅是傳入的customerid參數不一樣，再看看執行計劃，仍然是一樣，io也是一樣，就是返回的行數只有一行，按理來說，只返回一行，優化器應該會選擇使用非聚集索引，嵌套查找數據，但是優化器卻沒有好好利用customerid上的統計信息，仍然使用了聚集索引掃描，為什們？難道是索引上的統計信息不及時嗎？不，在手動使用fullscan后的統計信息仍然是一樣的查詢計劃，為什么呢？
因為sp_executesql本身就是一個存儲過程，他執行動態語句的參數是不會被利用上的，所以當第一次編譯的時候產生的計劃，存儲過程testexecutesql 是無法嗅探到的，即無法去引用customerid上的統計信息來做查詢計劃參考的，所以第一次編譯的查詢計劃是聚集索引掃描就是掃描，即使第二次執行的時候應該是查找。
如何才能改變這一現狀呢？
可以使用提示符，recompile強制讓存儲過程在執行的時候重新編譯，來獲得最好的執行計劃，不過這也是有代價的，就是每次都需要編譯，不過相比那些被浪費掉的IO，對一些大表的性能低下的查詢計劃還是很值得的。于是，我們把存儲過程改寫如下：

這樣再次執行exec testexecutesql 'CENTC'; exec testexecutesql 'SAVEA';
都能獲得一個最優的查詢計劃。

sql server能夠支持語句級的重編譯，自動嗅探重編譯環境，閥值，使得絕大部分情況下能夠很好的利用編譯后的查詢計劃，提高數據庫整體性能。我在08年初寫過的一個ppt，是關數據庫于重編譯的，大家可以下載看看，//img.cyzone.cn/temp/SQL SERVER 高級技巧系列之二：重編譯詳解.ppt   

如果有朋友關注數據庫性能方面的東西，可以加入我創建的一個小組，//home.cnblogs.com/group/sql/  歡迎提出自己遇到的性能問題。