共計 3143 個字符，預計需要花費 8 分鐘才能閱讀完成。
今天就跟大家聊聊有關 SQL Server 中怎樣執(zhí)行計劃緩存，可能很多人都不太了解，為了讓大家更加了解，丸趣 TV 小編給大家總結了以下內容，希望大家根據(jù)這篇文章可以有所收獲。
參數(shù)化和非參數(shù)化
查詢計劃的唯一標識是查詢語句本身，但假設語句的主體一樣，而僅僅是查詢條件謂詞不一樣，那在執(zhí)行計劃中算 1 個執(zhí)行計劃還是兩個執(zhí)行計劃呢？It s Depends。
假設下面兩個語句, 如圖 3 所示。
圖 3. 僅僅謂詞條件不一樣的兩個語句
雖然執(zhí)行計劃一樣，但是在執(zhí)行計劃緩存中卻會保留兩份執(zhí)行計劃，如圖 4 所示。
圖 4. 同一個語句，不同條件，有兩份不同的執(zhí)行計劃緩存
我們知道，執(zhí)行計劃緩存依靠查詢語句本身來判別緩存，因此上面兩個語句在執(zhí)行計劃緩存中就被視為兩個不同的語句。那么解決該問題的手段就是使得執(zhí)行計劃緩存中的查詢語句一模一樣。
參數(shù)化
使得僅僅是某些參數(shù)不同，而查詢本身相同的語句可以復用，就是參數(shù)化的意義所在。比如說圖 3 中的語句，如果我們啟用了數(shù)據(jù)庫的強制參數(shù)化，或是使用存儲過程等。SQL Server 會將這些語句強制參數(shù)話，比如說我們根據(jù)圖 5 修改了數(shù)據(jù)庫層級的選項。
圖 5. 數(shù)據(jù)庫層級的選項
此時我們再來執(zhí)行圖 3 中的兩條語句，通過查詢執(zhí)行計劃緩存，我們發(fā)現(xiàn)變量部分被參數(shù)化了，從而在計劃緩存中的語句變得一致，如圖 6 所示，從而可以復用.
圖 6. 參數(shù)話之后的查詢語句
但是，強制參數(shù)會引起一些問題，查詢優(yōu)化器很多時候就無法根據(jù)統(tǒng)計信息最優(yōu)化一些具體的查詢，比如說不能應用一些索引或者該掃描的時候卻查找。所產生的負面影響在上篇文章中已經說過，這里就不細說了。
因此對于上面的問題可以有幾種解決辦法。
平衡參數(shù)化和非參數(shù)化
在具體的情況下，參數(shù)化有些時候是好的, 但有些時候卻是性能問題的罪魁禍首，下面我們來看幾種平衡這兩者之間關系的手段。
使用 RECOMPILE
當查詢中，不準確的執(zhí)行計劃的成本要高于編譯的成本時，在存儲過程中使用 RECOMPILE 選項或是在即席查詢中使用 RECOMPILE 提示使得每次查詢都會重新生成執(zhí)行計劃，該參數(shù)會使得生成的執(zhí)行計劃不會被插入到執(zhí)行計劃緩存中。對于 OLAP 類查詢來說，不準確的執(zhí)行計劃所耗費的成本往往高于編譯成本太多，所以可以考慮該參數(shù)或選項，您可以如代碼清單 1 中的查詢所示這樣使用 Hint。
SELECT * FROM Sales.CustomerWHERE CustomerID 20000 AND TerritoryID = 4OPTION (recompile)代碼清單 1. 使用 Recompile
除去我們可以手動提示 SQL Server 重編譯之外，SQL Server 也會在下列條件下自動重編譯：
元數(shù)據(jù)變更，比如說表明稱改變、刪除列、變更數(shù)據(jù)類型等。統(tǒng)計信息變更。
連接的 SET 參數(shù)變化，SET ANSI_NULLS 等的值不一樣，會導致緩存的執(zhí)行計劃不能被復用，從而重編譯。這也是為什么我們看到緩存的執(zhí)行計劃中語句一模一樣，但就是不復用，還需要相關的參數(shù)一致，這些參數(shù)可以通過 sys.dm_exec_plan_attributes 來查看。
使用 Optimize For 參數(shù)
RECOMPILE 方式提供了完全不使用計劃緩存的節(jié)奏。但有些時候，特性謂語的執(zhí)行計劃被使用的次數(shù) h 更多，比如說，僅僅那些謂語條件產生大量返回結果集的參數(shù)編譯，我們可以考慮 Optimize For 參數(shù)。比如我們來看代碼清單 2。
DECLARE @vari INTSET @vari=4SELECT * FROM Sales.CustomerWHERE CustomerID 20000 AND TerritoryID = @variOPTION (OPTIMIZE FOR (@vari=4))代碼清單 2. 使用 OPTIMIZE FOR 提示
使用了該參數(shù)會使得緩存的執(zhí)行計劃按照 OPTIMIZE FOR 后面的謂語條件來生成并緩存執(zhí)行計劃，這也可能造成不在該參數(shù)中的查詢效率低下，但是該參數(shù)是我們選擇的，因此通常我們知道哪些謂語條件會被使用的多一些。
另外，自 SQL Server 2008 開始多了一個 OPTIMIZE FOR UNKNOWN 參數(shù)，這使得在優(yōu)化查詢的過程中探測作為謂語條件的局部參數(shù)的值，而不是根據(jù)局部變量的初始值去探測統(tǒng)計信息。
在存儲過程中使用局部變量代替存儲過程參數(shù)
在存儲過程中不使用過程參數(shù)，而是使用局部變量相當于直接禁用參數(shù)嗅探。畢竟，局部變量的值只有在運行時才能知道，在執(zhí)行計劃被查詢優(yōu)化器編譯時是無法知道該值的，因此強迫查詢分析器使用條件列的平均值進行估計。
雖然這種方式使得參數(shù)估計變得非常不準確，但是會變得非常穩(wěn)定，畢竟統(tǒng)計信息不會變更的過于頻繁。該方式不被推薦, 如果可能，盡量使用 Optimizer 的方式。
代碼清單 3 展示了這種方式。
CREATE PROC TestForLocalVari@vv INTASDECLARE @vari INTSET @vari=@vvSELECT * FROM Sales.CustomerWHERE CustomerID 20000 AND TerritoryID = @vari代碼清單 3. 直接引用局部變量，而不是存儲過程參數(shù)
強制參數(shù)化
在本篇文章的前面已經提到過了強制參數(shù)化，這里就不再提了。
使用計劃指導
在某些情況下，我們的環(huán)境不允許我們直接修改 SQL 語句，比如所不希望破壞代碼的邏輯性或是應用程序是第三方開發(fā)，因此無論是加 HINT 或參數(shù)都變得不現(xiàn)實。此時我們可以使用計劃指導。
計劃指導使得查詢語句在由客戶端應用程序扔到 SQL Server 的時候，SQL Server 對其加上提示或選項，比如說通過代碼清單 4 可以看到一個計劃指導的例子。
EXEC sp_create_plan_guide N MyPlanGuide1 ,@stmt=N SELECT * FROM Sales.Customer WHERE CustomerID 20000 AND TerritoryID=@vari ,@type=N sql ,@module_or_batch=NULL,@params=N @vari int ,@hints=N OPTION (RECOMPILE)代碼清單 4. 對我們前面的查詢設置計劃指導
當加入了計劃指導后，當批處理到達 SQL Server 時，在查找匹配的計劃緩存時也會去找是否有計劃指導和其相匹配。如果匹配，則應用計劃指導中的提示或選項。這里要注意的是，這里 @stmt 參數(shù)必須和查詢語句中的一句一模一樣，差一個空格都會被認為不匹配。
PARAMETERIZATION SIMPLE
當我們在數(shù)據(jù)庫層級啟用了強制參數(shù)化時，對于特定語句，我們卻不想啟用強制參數(shù)化，我們可以使用 PARAMETERIZATION SIMPLE 選項，如代碼清單 5 所示。
DECLARE @stmt NVARCHAR(MAX)DECLARE @params NVARCHAR(MAX)EXEC sp_get_query_template N SELECT * FROM Sales.Customer WHERE CustomerID 20000 AND TerritoryID=2 ,@stmt OUTPUT, @params OUTPUTPRINT @stmtPRINT @paramsEXEC sp_create_plan_guide N MyTemplatePlanGuide , @stmt, N TEMPLATE , NULL, @params, N OPTION(PARAMETERIZATION SIMPLE)看完上述內容，你們對 SQL Server 中怎樣執(zhí)行計劃緩存有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注丸趣 TV 行業(yè)資訊頻道，感謝大家的支持。
