在這一期的性能調優教育訓練裡，我想詳細談下SQL Server裡計劃緩存及其副作用。在上一周你已經學到，每個送出給SQL Server的邏輯查詢會編譯成實體執行計劃。那個執行計劃然後會被緩存，即被稱為計劃緩存，用作後期的重用。首先我們來看下即席SQL語句（adhoc SQL statements，對應的反義詞：prepared SQL statements）的副作用，即帶來的性能問題。
即席SQL語句（adhoc SQL statements）
每次當你送出一個即席SQL語句到SQL Server時，對每個唯一的查詢，都會有執行計劃被編譯。“唯一的查詢”是什麼意思？答案很簡單：SQL Server對完整的SQL語句（包括可能寫死的參數值）生成一個hash值，并使用這個hash值作為在計劃緩存裡查找值。如果找到這個值的執行計劃，這個計劃就會被重用，否則的話新的計劃會被編譯并最後在計劃緩存裡緩存。看下我們送出下面這3個查詢給SQL Server：
1 --清空計劃緩存
2 DBCC FREEPROCCACHE;
3
4 SELECT * FROM Sales.SalesOrderHeader
5 WHERE CustomerID = 11000
6 GO
7
8 SELECT * FROM Sales.SalesOrderHeader
9 WHERE CustomerID = 30052
10 GO
11
12 SELECT * FROM Sales.SalesOrderHeader
13 WHERE CustomerID = 11223
14 GO
15 --檢視緩存的執行計劃
16 SELECT * FROM sys.dm_exec_cached_plans
17
18 --檢視對應plan_handle的對應SQL語句
19 SELECT * FROM sys.dm_exec_sql_text(0x06000B0028382622B800CB0A000000000000000000000000)
20 SELECT * FROM sys.dm_exec_sql_text(0x06000B00E2CE4D15B820AE0A000000000000000000000000)
21 SELECT * FROM sys.dm_exec_sql_text(0x06000B005C407727B820C90A000000000000000000000000)
22 SELECT * FROM sys.dm_exec_sql_text(0x06000B00B4D38136B8200908000000000000000000000000) 
對這3個查詢，SQL Server會編譯3個不同的執行計劃，因為你提供寫死的參數值。是以計算出來的hash值在3個查詢之間是不同的，找不到被緩存的計劃。作為一個副作用，對于幾乎相同的查詢，你有3個執行計劃。這個問題被稱為計劃緩存污染（Plan Cache Pollution） 。
你用不同的執行計劃污染了你的計劃緩存，這些計劃是不能被重用的（因為寫死的參數值），并且你在浪費大量有用的記憶體，這些記憶體在SQL Server裡可以被其他元件使用。緩存的目的應該是持續數次的高重用，特定SQL語句不屬于這個情況。
計劃穩定性
如果你參數話你的SQL語句，或者使用存儲過程。在那個情況下，SQL Server可以非常容易的重用執行計劃。但是即使重用執行計劃也會帶來性能的問題。比如SQL Server為一個查詢編譯了一個需要執行書簽查找的執行計劃，因為用到的非聚集索引沒有覆寫到查詢字段。
在第8周我們說過，如果你從表擷取少量資料，書簽查找還是有用的。當你越過臨界點時，使用全表/索引掃描将更高效。但是SQL Server如果重用緩存的執行計劃，就不會考慮這個選擇了——SQL Server隻會盲目的重用你的計劃——即使性能非常糟糕！我們看看下面的實際執行計劃：
這裡SQL Server盲目重用了包含書簽查找的被緩存的計劃。如你所見，估計行數（estimated number of rows ）和實際行數（actual number of rows ）完全不同。SQL Server基于假設那個查詢隻傳回1條記錄來編譯和緩存了計劃。但是實際上SQL Server傳回了1499條記錄。看看執行計劃，我們會更清晰，優化器是假設隻傳回1條記錄才執行這個操作的。
這會導緻你沒有計劃穩定性。基于估計行數，你得到書簽查找的緩存計劃，要不就是如果越過臨界點的話是全表/索引掃描。這個是我們在性能調優時經常碰到的性能問題。
如果修正這個問題呢？很簡單：通過覆寫非聚集索引來避免書簽查找。用這個方法你就得到了計劃穩定性，不管你輸入的第一個參數是什麼值都會有同樣的計劃和性能。如果你對這個問題感興趣，可以看下參數嗅探（Parameter Sniffing）（1/2) 和參數嗅探（Parameter Sniffing）（2/2) 。
小結
在這期的性能調優教育訓練裡，你看到計劃緩存是個雙刃劍：一方面，計劃緩存非常強大，因為你可以重用已經編譯的計劃來避免編譯成本；另一方面，它非常危險，因為定型的執行計劃，你沒有計劃穩定性，即你不能保證的性能始終如一。
希望這次教育訓練你有所收獲，下周我們會詳細介紹SQL Server中的重編譯。請繼續關注！
圍觀PPT：
0727_10_計劃緩存.rar
注：此文章為
WoodyTu學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！
若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！