[sql server] 執行計畫的緩衝和重新使用

執行計畫的緩衝和重新使用

 

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 語句中的第一個語句與現有計劃不匹配，而第二個語句則匹配：

	{ CopyCode(this) }" onmouseover="function onmouseover() { ChangeCopyCodeIcon(this) }" onmouseout="function onmouseout() { ChangeCopyCodeIcon(this) }" onkeypress="function onkeypress() { CopyCode_CheckKey(this) }">複製代碼
SELECT * FROM Person.ContactSELECT * FROM AdventureWorks.Person.Contact

在 SQL Server 2000 和 SQL Server 2005 執行個體中，個別執行計畫重新使用的機率比在 SQL Server 6.5 及更早版本中高。

{
ExpandCollapse(sectionToggle0)
}" onkeypress="function onkeypress()
{
ExpandCollapse_CheckKey(sectionToggle0)
}">執行計畫的老化

產生執行計畫後，它處於過程緩衝中。只有當需要空間時，SQL Server 2005 才使舊的未用計劃從快取老化掉。每個查詢計劃和執行環境都有相關的成本因子，可表明編譯結構所需的費用。這些資料結構還有一個年齡欄位。對象每由串連引用一次，其年齡欄位便按編譯成本因子遞增。例如，如果查詢計劃的成本因子為 8 並且被引用了兩次，則其年齡變為 16。惰性寫入器進程定期掃描過程緩衝中的對象列表。然後，惰性寫入器減少每個對象的年齡欄位，每掃描一次減少 1。在本例中，查詢計劃的年齡經過 16 次過程緩衝掃描後減為 0，除非其他使用者引用了該計劃。如果滿足下面三個條件，惰性寫入器進程將釋放對象：

• 記憶體管理器需要記憶體而所有可用記憶體都正在使用。
• 對象的年齡欄位是 0。
• 對象在當前沒有被串連引用。

因為每次引用對象時其年齡欄位都會增加，所以經常被引用的對象的年齡欄位不會減為 0，也不會從快取老化掉。不經常被引用的對象將很快滿足釋放條件，但是不會真被釋放，除非其他對象有記憶體需求。

{
ExpandCollapse(sectionToggle1)
}" onkeypress="function onkeypress()
{
ExpandCollapse_CheckKey(sectionToggle1)
}">重新編譯執行計畫

根據資料庫的新狀態，資料庫內的某些更改可能會導致執行計畫效率低下或不再有效。SQL Server 檢測這些使執行計畫無效的更改，並將計劃標記為無效。此後，必須為執行查詢的下一個串連重新編譯新的計劃。導致計劃無效的情況包括：

• 對查詢所引用的表或視圖變更（ALTER TABLE 和 ALTER VIEW）。
• 對執行計畫所使用的任何索引變更。
• 對執行計畫所使用的統計資訊進行更新，該更新可能是從語句（如 UPDATE STATISTICS）中顯示產生，也可能是自動產生的。
• 刪除執行計畫所使用的索引。
• 顯式調用 sp_recompile。
• 對鍵的大量更改（其他使用者對由查詢引用的表使用 INSERT 或 DELETE 語句所產生的修改）。
• 對於帶觸發器的表，插入的或刪除的表內的行數顯著增長。

為了使語句正確，或要獲得可能更快的查詢執行計畫，大多數都需要進行重新編譯。

在 SQL Server 2000 中，如果批處理中的某條語句導致了重新編譯，則不管是通過預存程序、觸發器、特殊批處理提交，還是通過準備好的語句提交，都將重新編譯整個批處理。在 SQL Server 2005 中，只有在批處理中導致重新編譯的語句才會被重新編譯。因為這個差異，在 SQL Server 2000 中將對重新編譯計數，而在 SQL Server 2005 中則不需要。而且，在 SQL Server 2005 中有更多類型的重新編譯，因為它擴充了功能集。

語句級重新編譯有助於提高效能，因為在大多數情況下，只有少數語句導致了重新編譯並造成相關損失（指 CPU 時間和鎖）。因此，避免了批處理中其他不必重新編譯的語句的這些損失。

在 SQL Server 2005 中，SQL Server Profiler SP:Recompile 跟蹤附隨報告語句級重新編譯。在 SQL Server 2000 中，此跟蹤事件只報告批處理重新編譯。而且，在 SQL Server 2005 中，將填充此事件的 TextData 列。因此，不再需要 SQL Server 2000 中所使用的方法：必須跟蹤 SP:StmtStarting 或 SP:StmtCompleted 以獲得導致重新編譯的 Transact-SQL 文本。

SQL Server 2005 也添加了一個新跟蹤事件，稱為 SQL:StmtRecompile，它報告語句級重新編譯。它還可用於跟蹤和調試重新編譯。只為預存程序和觸發器產生 SP:Recompile ，而為預存程序、觸發器、特殊批處理、使用 sp_executesql 執行的批處理、準備好的查詢以及動態 SQL 產生 SQL:StmtRecompile。

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 時，不會發生基於統計資訊或基數變化的重新編譯。請注意，在 SQL Server 2000 中，即使此設定為 OFF，查詢仍然會基於 DML 觸發程序 inserted 和 deleted 表的基數變化進行重新編譯。有關禁用 AUTO_UPDATE_STATISTICS 的詳細資料，請參閱索引統計資訊。