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

使用northwind數(shù)據(jù)庫，
執(zhí)行：
select * from orders where customerid = 'SAVEA';

執(zhí)行：
select * from orders where customerid = 'CENTC';

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


創(chuàng)建如下存儲過程：

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

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

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

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

這樣再次執(zhí)行exec testexecutesql 'CENTC'; exec testexecutesql 'SAVEA';
都能獲得一個最優(yōu)的查詢計劃。

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

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