執行計劃快取與重複使用

更新： 2006 年 7 月 17 日

SQL Server 2005 具有一個記憶體集區，可用來儲存執行計劃以及資料緩衝區。配置給執行計劃或資料緩衝區的集區百分比，會依系統的狀態而動態調整。記憶體集區中用來儲存執行計劃的那一部分，稱為程序快取。

SQL Server 2005 執行計劃具有下列主要元件：

• 查詢計劃
  大多數的執行計劃是可重新進入的唯讀資料結構，而且可供任意數目的使用者所使用。此稱為查詢計劃。查詢計劃中並不會儲存任何使用者內容。記憶體中絕不會有超過一或兩個的查詢計劃副本：一個是所有序列執行的副本，另一個則是所有平行執行的副本。平行副本會涵蓋所有的平行執行，不論其平行處理原則的程度為何。
• 執行內容
  目前執行查詢的每位使用者都有資料結構，其中保存了與其執行相關的特定資料，例如參數值。此資料結構即稱為執行內容。而此執行內容資料結構將會重複使用。如果使用者執行查詢，而且其中有一個結構不在使用中，則系統會根據新使用者的內容來重新初始化該結構。

在 SQL Server 2005 中執行任何 SQL 陳述式時，關聯式引擎會先尋找整個程序快取，以確認相同 SQL 陳述式的現有執行計劃是否存在。如果 SQL Server 2005 找到現有的計劃，就會重複使用它，如此可省下重新編譯 SQL 陳述式的負擔。如果沒有現有的執行計劃，SQL Server 2005 會為查詢建立新執行計劃。

SQL Server 2005 有一個非常有效率的演算法，可為任何特定 SQL 陳述式尋找現有的執行計劃。在大部分的系統中，這個掃描所使用的最少資源，比能夠重複使用現有計劃來取代編譯每個 SQL 陳述式所節省下來的資源還少。

此演算法若要能使得新的 SQL 陳述式符合快取中現有、未使用的執行計劃，所有的物件參考必須是完整的。例如，這些 SELECT 陳述式的第一個不符合現有計劃，而第二個則符合：

SELECT * FROM Contact

SELECT * FROM Person.Contact

個別執行計劃在 SQL Server 2000 及 SQL Server 2005 的執行個體中重複使用的機率，比在 SQL Server 6.5 版和之前版本來得高。

舊的執行計劃

產生執行計劃後，它就會留在程序快取中。只有在需要空間時，SQL Server 2005 才會刪除快取中未使用的舊計劃。每個查詢計劃和執行內容都有相關的成本因數，可指出編譯該結構有多昂貴。這些資料結構同時也有一個時間欄位。每次連接參考物件時，編譯成本因數便會在時間欄位遞增一個單位。例如，如果查詢計劃具有成本因數 8，而且被參考兩次，則其存在時間會變成 16。遲緩寫入器處理序會定期掃描程序快取中的物件清單。每掃描一次，遲緩寫入器便會將各物件的時間欄位減 1。範例查詢計劃時間在掃描 16 次程序快取區後，將遞減為 0，除非有其他使用者參考此計劃。遲緩寫入器處理序在遇到下列情況時，會解除配置物件：

• 記憶體管理員需要記憶體，但所有可用的記憶體目前都在使用中。
• 物件的時間欄位為 0。
• 物件目前沒有被任何連接所參考。

因為每參考一次物件，時間欄位便會加一，所以經常參考的物件，其時間欄位便不會遞減至 0，也因此不會自快取區中刪除。不常參考的物件，很快地就會列為解除配置的對象，但除非其他物件需要記憶體，否則並不會實際解除該物件的配置。

重新編譯執行計劃

根據資料庫的新狀態，資料庫中的特定變更會造成執行計劃沒有效率或無效。SQL Server 偵測到使執行計劃無效的變更，並將該計劃標示為無效。然後系統會根據執行查詢的下一個連接，重新編譯新的計劃。會使計劃無效的狀況包括：

• 對查詢所參考之資料表或檢視所做的變更 (ALTER TABLE 和 ALTER VIEW)。
• 對執行計劃所使用之任何索引所做的變更。
• 對執行計劃所使用統計資料的更新，這些更新是由 UPDATE STATISTICS 之類的陳述式明確地產生，或是自動產生。
• 卸除執行計劃所使用的索引。
• 對 sp_recompile 的明確呼叫。
• 對鍵值的大幅變更 (由其他修改查詢所參考之資料表的使用者所產生的 INSERT 或 DELETE 陳述式)。
• 對於含有觸發程序的資料表，是指如果 inserted 或 deleted 資料表中的資料列數目顯著增加的情況。
• 使用 WITH RECOMPILE 選項執行預存程序。

不管是為了讓陳述式正確或是要取得可能更快的查詢執行計劃，多數的重新編譯都是必要的。

在 SQL Server 2000 中，每當批次內的陳述式造成重新編譯時，無論是透過預存程序、觸發程序、特定批次或準備陳述式送出，都會重新編譯整個批次。而在 SQL Server 2005 中，則只重新編譯批次內導致重新編譯的陳述式。因為這項差異，故無法比較 SQL Server 2000 與 SQL Server 2005 中的重新編譯計數。此外，SQL Server 2005 已擴充功能集，所以有更多種重新編譯類型。

陳述式層級的重新編譯有益於效能，因為在大部分情況下，只有少量的陳述式會導致重新編譯並造成相關負面影響，也就是 CPU 時間及鎖定。批次中不必重新編譯的其他陳述式則可避免這些負面影響。

SQL Server Profiler SP:Recompile 追蹤事件會報告 SQL Server 2005 中的陳述式層級重新編譯。此追蹤事件只會報告 SQL Server 2000 中的批次重新編譯。不僅如此，在 SQL Server 2005 中，還在此事件的 TextData 資料行中填入資料。因此，在 SQL Server 2000 中必須追蹤 SP:StmtStarting 或 SP:StmtCompleted，以取得造成重新編譯之 Transact-SQL 文字的作法，現在已不再需要。

SQL Server 2005 另加入了新的追蹤事件 (稱為 SQL:StmtRecompile)，可報告陳述式層級重新編譯。此追蹤事件可以用來追蹤及偵錯重新編譯。SP:Recompile 只能針對預存程序及觸發程序來產生；相較之下，SQL:StmtRecompile 則可針對預存程序、觸發程序、特定批次、使用 sp_executesql 所執行的批次、準備查詢及動態 SQL 來產生。

SP:Recompile 及 SQL:StmtRecompile 的 EventSubClass 資料行含有一個整數碼，來指出重新編譯的原因。下表列出每一個代碼的意義。

附註：

當 AUTO_UPDATE_STATISTICS 資料庫選項設為 ON，其目標資料表或索引檢視的統計值或基數明顯和上次執行不同時，就會重新編譯查詢。此行為適用於標準使用者自訂資料表、暫存資料表，以及 DML 觸發程序建立的 inserted 和 deleted 資料表。如果過多的重新編譯影響了查詢效能，請考慮將此設定值變更為 OFF。當 AUTO_UPDATE_STATISTICS 資料庫選項設為 OFF 時，不會發生基於統計資料或基數變更的重新編譯，唯一例外的是 inserted 和 deleted 資料表，它們是由 DML INSTEAD OF 觸發程序所建立的。因為這些資料表是在 tempdb 中建立的，所以存取它們的查詢是否重新編譯，視 tempdb 中的 AUTO_UPDATE_STATISTICS 設定而定。請注意，在 SQL Server 2000 中，即使此設定值為 OFF，還是會繼續根據 DML 觸發程序 inserted 和 deleted 資料表的基數變更重新編譯查詢。如需有關停用 AUTO_UPDATE_STATISTICS 的詳細資訊，請參閱＜索引統計資料＞。

請參閱

參考

SQL Server 的 SQL Statistics 物件

概念

緩衝區管理

其他資源

SQL Server 2005 中的批次編譯、重新編譯及計劃快取問題 (英文)

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄