今天我想進一步談下SQL Server裡的計劃緩存和它的副作用。我們都知道，每個送出到SQL Server的邏輯查詢，會編譯為實體執行計劃。這個執行計劃然後會緩存為所謂的計劃緩存，用于後期重用。現在我們首先來談下即席SQL語句和它的負作用，還有它們帶來的性能問題。
即席SQL語句（Adhoc SQL Statements）
每次當你送出一個即席SQL語句到SQL Server，對于每個特定查詢，都會生成一個執行計劃。“特定查詢”是什麼意思？答案很簡單：SQL Server對每個完整的SQL語句（包括你的參數值）生成一個哈希值，并使用這個哈希值作為計劃緩存的查找值。如果使用這個哈希值找到一個執行計劃，計劃就會重用，否則在計劃緩存裡會編譯一個新的執行計劃。假設你送出下列3個查詢到SQL Server：
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID = 11000
GO
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID = 30052
GO
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID = 11223
GO 對于這3個查詢，SQL Server編譯3個不同的執行計劃，因為你提供了寫死的參數值。是以3個查詢間會計算不同的的哈希值，不會找到已緩存的計劃。作為副作用，在計劃緩存裡，現在你有近3個近乎一樣的查詢有3個不同的計劃。這個特定問題被稱為計劃緩存污染。
你剛用不同的執行計劃污染了你的計劃緩存，這很難重用（因為寫死的參數值），而且你在浪費可以被SQL Server裡其它元件使用的有用記憶體。緩存的目的應該提高重用數，但使用即席SQL語句就做不到。
計劃穩定性
假設你為你的SQL語句使用參數值，或者你甚至使用存儲過程。在這個情況下，SQL Server非常容易重用緩存的執行計劃。但即使重用緩存的執行計劃，你回引入性能問題。例如SQL Server為一個查詢編譯了一個執行計劃，它回進行書簽查找，因為非聚集索引沒有覆寫你的查詢：
我們提過，書簽查找隻有在從表裡擷取一些資料才有意義。如果你越過了臨界點，使用全表掃描或聚集索引掃描更高效。但如果SQL Server沖了緩存的執行計劃，這個選項就不會考慮太多——SQL Server會盲目重用你的計劃——這時你的性能就會很差！可以看下面的例子：
這裡SQL盲目重用了有書簽查找的緩存計劃。你會看到估計和實際行數有很大差别！SQL Server在假設從查詢裡隻傳回一條記錄來編譯和緩存計劃。但實際上從SQL Server我們拿回1499條記錄。你看到的執行計劃，是假設隻有一條記錄傳回的情況下優化——考慮下這個情況。
這裡潛在的根源是你的計劃不穩定。基于估計行數，你得到有書簽查找的緩存計劃，如果你越過臨界點，會是表/聚集索引掃描。這是我們經常碰到的常見SQL Server性能問題。
你如何解決這個問題？簡單：通過覆寫非聚集索引來避免書簽查找。使用這個方法你會獲得計劃穩定性，不管你的輸入參數，你會得到同樣的性能。
小結
今天你學到了SQL Server裡，計劃緩存的雙刃劍：在一方面，計劃緩存非常強大，因為你可以重用計劃緩存避免編譯資源占用。在另一方面，他非常危險，使用定型的執行計劃，你的計劃不再穩定，這就意味着你不能再保證性能。
感謝關注！
原文連結
https://www.sqlpassion.at/archive/2017/03/20/plan-caching/
注：此文章為
WoodyTu學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！
若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！