sqLSERVER 計畫快取

標籤：

在這一期的效能調優培訓裡，我想詳細談下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 SELECT * FROM Sales.SalesOrderHeader 2  3 WHERE CustomerID = 11000 4  5 GO 6  7 SELECT * FROM Sales.SalesOrderHeader 8  9 WHERE CustomerID = 3005210 11 GO12 13 SELECT * FROM Sales.SalesOrderHeader14 15 WHERE CustomerID = 1122316 17 GO

對這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條記錄才執行這個操作的。

這會導致你沒有計劃穩定性。基於估計行數，你得到書籤尋找的緩衝計劃，要不就是如果越過臨界點的話是全表/索引掃描。這個是我們在效能調優時經常碰到的效能問題。

sqLSERVER 計畫快取