共用方式為


執行計劃快取與重複使用

更新: 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 陳述式)。
  • 對於含有觸發程序的資料表,是指如果 inserteddeleted 資料表中的資料列數目顯著增加的情況。
  • 使用 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:StmtStartingSP:StmtCompleted,以取得造成重新編譯之 Transact-SQL 文字的作法,現在已不再需要。

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

SP:RecompileSQL:StmtRecompileEventSubClass 資料行含有一個整數碼,來指出重新編譯的原因。下表列出每一個代碼的意義。

EventSubClass 值 描述

1

結構描述已變更。

2

統計資料已變更。

3

延遲編譯。

4

SET 選項已變更。

5

暫存資料表已變更。

6

遠端資料列集已變更。

7

FOR BROWSE 權限已變更。

8

查詢通知環境已變更。

9

資料分割檢視已變更。

10

資料指標選項已變更。

11

OPTION (RECOMPILE) 已要求。

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

請參閱

參考

SQL Server 的 SQL Statistics 物件

概念

緩衝區管理

其他資源

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

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本 歷程記錄

2006 年 7 月 17 日

新增內容:
  • 在可使計劃無效之狀況的清單中新增「使用 WITH RECOMPILE 選項執行預存程序」一項。

2005 年 12 月 5 日

新增內容:
  • 釐清有關 AUTO_UPDATE_STATISTICS 資料庫選項設為 OFF 時查詢重新編譯的附註,它將以不同方式套用至 DML INSTEAD OF 觸發程序所建立的 inserteddeleted 資料表。