共用方式為

  • 發行項

執行計畫快取與重複使用

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

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

  • 查詢計畫

    大多數的執行計畫是可重新進入的唯讀資料結構，而且可供任意數目的使用者所使用。此稱為查詢計畫。查詢計畫中並不會儲存任何使用者內容。記憶體中絕不會有超過一或兩個的查詢計畫副本：一個是所有序列執行的副本，另一個則是所有平行執行的副本。平行副本會涵蓋所有的平行執行，不論其平行處理原則的程度為何。

  • 執行內容

    目前執行查詢的每位使用者都有資料結構，其中保存了與其執行相關的特定資料，例如參數值。此資料結構即稱為執行內容。而此執行內容資料結構將會重複使用。如果使用者執行查詢，而且其中有一個結構不在使用中，則系統會根據新使用者的內容來重新初始化該結構。

執行內容、相同查詢、不同的常值

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

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

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

SELECT * FROM Contact

SELECT * FROM Person.Contact

從程序快取中移除執行計畫

只要記憶體足以存放執行計畫，執行計畫就會保留在程序快取中。當記憶體不足的壓力存在時，Database Engine 就會使用以成本為基礎的方法來判斷要從程序快取中移除哪些執行計畫。為了進行以成本為基礎的決策，Database Engine 會根據下列因素，針對每個執行計畫增加和減少目前的成本變數。

當使用者處理序將執行計畫插入快取時，該使用者處理序會將目前的成本設定為等於原始查詢編譯成本。若為特定執行計畫，使用者處理序則會將目前成本設定為零。因此，每當使用者程序參考執行計畫時，它都會將目前成本重設為原始編譯成本；如果是特定執行計畫，使用者程序會增加目前成本。對於所有計畫而言，目前成本的最大值就是原始編譯成本。

當記憶體不足的壓力存在時，Database Engine 會從程序快取中移除執行計畫，藉此進行回應。為了判斷要移除哪些計畫，Database Engine 會重複檢查每個執行計畫的狀態，然後移除目前成本為零的計畫。當記憶體不足的壓力存在時，系統不會自動移除目前成本為零的執行計畫。只有當 Database Engine 檢查計畫並且發現目前成本為零時，才會移除此計畫。檢查執行計畫時，如果查詢目前沒有使用計畫，Database Engine 就會減少目前成本，藉此將目前成本推向零。

Database Engine 會重複檢查執行計畫，直到移除足夠的計畫，可滿足記憶體需求為止。當記憶體不足的壓力存在時，執行計畫可能會多次增加和減少其成本。當記憶體不足的壓力消失時，Database Engine 就會停止減少未使用之執行計畫的目前成本，而且所有執行計畫都會保留在程序快取中，即使其成本為零也一樣。

為了回應記憶體壓力，Database Engine 會使用資源監視器和使用者執行緒來釋放程序快取中的記憶體。資源監視器和使用者執行緒可以檢查計畫並同時執行，以便針對每個未使用的執行計畫減少目前成本。當全域記憶體不足的壓力存在時，資源監視器就會從程序快取中移除執行計畫。它會釋放記憶體，以便強制執行系統記憶體、處理序記憶體、資源集區記憶體和所有快取大小上限的原則。

所有快取大小上限是緩衝集區大小的函數，而且不能超過伺服器記憶體的最大值。如需有關設定伺服器記憶體最大值的詳細資訊，請參閱＜sp_configure (Transact-SQL)＞中的＜max server memory 設定＞。

當單一快取記憶體不足的壓力存在時，使用者執行緒就會從程序快取中移除執行計畫。它們會強制執行最大單一快取大小和最大單一快取項目的原則。

下列範例說明要從程序快取中移除哪些執行計畫：

  • 執行計畫經常被參考，所以它的成本永遠都不會變成零。計畫依然在程序快取中，而且除非有記憶體不足的壓力且目前成本為零，否則不會移除計畫。

  • 系統會插入特定執行計畫，但在記憶體不足的壓力存在之前，不會再次參考它。由於特定計畫會以目前成本為零來初始化，所以當資料庫引擎檢查執行計畫時，它將會看到目前成本為零，並從程序快取中移除此計畫。當記憶體壓力不存在時，特定執行計畫會留在程序快取中，且目前成本為零。

若要手動從快取中移除單一計畫或所有計畫，請使用＜DBCC FREEPROCCACHE (Transact-SQL)＞。

重新編譯執行計畫

根據資料庫的新狀態，資料庫中的特定變更會造成執行計畫沒有效率或無效。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 和更新版本已擴充功能集，所以有更多種重新編譯類型。

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

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

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

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

EventSubClass 值

說明

1

結構描述已變更。

2

統計資料已變更。

3

延遲編譯。

4

SET 選項已變更。

5

暫存資料表已變更。

6

遠端資料列集已變更。

7

FOR BROWSE 權限已變更。

8

查詢通知環境已變更。

9

資料分割檢視已變更。

10

資料指標選項已變更。

11

OPTION (RECOMPILE) 已要求。

[!附註]

當 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 的詳細資訊，請參閱＜使用統計資料來改善查詢效能＞。

變更記錄

更新的內容

已經更新有關從程序快取中移除執行計畫的章節。