查詢計劃

Sql Server在執行一條查詢語句之前都對對它進行“編譯”并生成“查詢計劃”，查詢計劃告訴Sql Server的查詢引擎應該用什么方式進行工作。Sql Server會根據當前它可以收集到的各種信息（例如內存大小，索引的統計等等）把一條查詢語句編譯成它認為“最優”的查詢計劃。很顯然，得到這樣一個查詢計劃需要消耗CPU資源，而大部分的查詢語句每次經過編譯所得到的查詢計劃往往是相同的，因此除非指定了RECOMPILE選項，Sql Server在執行查詢語句時，會對查詢計劃進行緩存——也就是說，如果是相同的查詢語句，Sql Server只會對它進行一次編譯操作，然后在每次執行時對查詢計劃進行復用。查詢計劃如果無法復用，則會在相當程度上降低數據庫性能——因為過多的CPU被消耗在查詢語句的編譯上。各種提及數據庫查詢優化的資料上大都會提到這一點，我們往往通過查看性能計數器的某些統計，或者Sql Server系統表中的一些記錄，就可以判定您的數據庫應用是否出現了這個問題。

對于存儲過程來說，復用查詢計劃是輕而易舉的。不過對于那些喜歡在程序代碼中拼接Sql字符串的朋友來說，日子就有些不好過了。Sql Server是根據您傳入的Sql語句來緩存查詢計劃的，如果您“強行”拼接了Sql字符串并交給Sql Server執行，那么查詢計劃被復用的可能性微乎其微。因此，我們絕對應該杜絕拼接字符串的行為，因為這不僅僅造成了傳統的Sql注入！而那些習慣相對較好的朋友，則會使用帶參數的Sql語句，在交給Sql Server執行時就可能復用查詢計劃。因為和調用存儲過程相比，發送帶參數的Sql語句只是將使用了sp_executesql命令而已，每次執行的查詢語句還是相同的。

問題何在？

對于復用查詢計劃的問題，在上文中我說了這么一句話：“……使用帶參數的Sql語句，在交給Sql Server執行時就可能復用查詢計劃……”。我為什么要說“可能”？因為即時使用帶參數的Sql語句，在某些情況下我們還是無法對查詢計劃進行復用。這是怎么一回事兒呢？我們還是直接從Linq to Sql來產生Sql語句，然后觀察Sql Server的行為吧。

請看以下的代碼（示例所操作的數據表與《在Linq to Sql中管理并發更新時的沖突（2）：引發更新沖突》一文相同）：

LinqToSqlDemoDataContext dataContext = new LinqToSqlDemoDataContext();dataContext.Log = Console.Out;Video video1 = dataContext.Videos.SingleOrDefault(    v => v.Introduction == "Hello");Video video2 = dataContext.Videos.SingleOrDefault(    v => v.Introduction == "Hello World");Console.ReadLine();

還是查看輸出：

SELECT [t0].[VideoID], [t0].[Introduction], [t0].[SiteID]FROM [dbo].[Video] AS [t0]WHERE [t0].[Introduction] = @p0-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Hello]-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21004.1SELECT [t0].[VideoID], [t0].[Introduction], [t0].[SiteID]FROM [dbo].[Video] AS [t0]WHERE [t0].[Introduction] = @p0-- @p0: Input NVarChar (Size = 11; Prec = 0; Scale = 0) [Hello World]-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21004.1 

兩句Sql語句完全相同，按我們剛才的說法，Sql Server應該緩存了查詢計劃。但是我們通過查看sys.syscacheobjects的相關數據可以看出，事情并非如同我們想象的那樣：

SELECT cheacobjtype, sql FROM sys.syscacheobjects;DBCC freeproccache;

請注意上圖中被選中的兩條記錄，它表明了Sql Server并沒有緩存執行計劃。

為什么？這兩次執行究竟有什么區別？通過Linq to Sql很容易看出，兩次執行所用到的參數不同。更進一步，如果對比Linq to Sql輸出的緩存以及sys.syscacheobjects視圖中的記錄，就會發現：其實僅僅是參數的尺寸不同。

沒錯，就是這個原因。在使用ADO.NET時，如果SqlParameter的Type是nvarchar，并且沒有指定Size屬性，則可能就會因為具體參數的尺寸不同而造成查詢計劃無法復用的結果。這一點，很多人都忽視了。

優化方案

在使用ADO.NET進行開發時，該問題其實很容易解決。我們只要指定SqlParameter的Size屬性即可。由于每次指定了一個固定的參數尺寸，Sql Server就能夠復用查詢計劃了。

不過我們現在在使用Linq to Sql，又該怎么做呢？嗯，我們可以為XXXXDataContext重寫（override）SubmitChanges方法，在其中獲得需要執行的SqlCommand對象（具體方法請參考《在Linq to Sql中管理并發更新時的沖突（1）：預備知識》一文），獲得其中的SqlParameter參數，并設定它們的Size屬性。我們可以使用Custom Attribute來標注應該為哪個屬性設置什么樣的Size，如果再結合AOP，哈哈……

等等，先別想那么遠。即使得到了SqlCommand對象，它所生成的Sql語句是以@p0、@p1作為參數名，您知道該修改哪個SqlParameter對象嗎？再者，SubmitChanges方法只是提交我們做出的修改，但是在一般的系統中，查詢操作的次數和性能消耗大大超過修改操作，而重寫了SubmitChangeds方法又不能影響我們的優化操作……

因此，我想在這里說的是：這個問題我們沒法進行優化。

不過我們還是幸運的，因為我根據我的經驗，似乎在查詢條件中使用長度不等的字符串作為參數的情況并不多見。不是么？

it知識庫：LINQ to SQL的執行可能無法復用查詢計劃，轉載需保留來源！

鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。