文章編號: 243586 - 上次校閱: 2006年9月6日 - 版次: 2.2

預存程序重新編譯問題疑難排解

檢視此文章適用的產品。
系統提示本文適用於您使用的作業系統之外的作業系統。與您不相關的文章內容已停用。

在此頁中

全部展開 | 全部摺疊

結論

本文將會探討應用程式可能會在 Microsoft SQL Server 中遭遇到的一種特殊效能問題：預存程序在執行階段會重新編譯。如果您正在進行效能問題的疑難排解，但是尚未判定這就是問題的核心根源，請先參閱下列「Microsoft 知識庫」中的文件，再繼續進行：

224587? (http://support.microsoft.com/kb/224587/ ) HOW TO：SQL Server 應用程式效能疑難排解
本文假定您已使用上述文件縮小問題的範圍，而且也已擷取 SQL Server Profiler 追蹤 (其中包含特定事件及資料行的詳細資訊)。
回此頁最上方

其他相關資訊

當使用者執行預存程序時，如果快取中尚無法使用該程序，SQL Server 便會載入該程序並編譯查詢計劃。已編譯的計劃都會儲存在快取中，並且由後續的預存程序呼叫者重新使用，直到發生某些動作，讓計劃失效而強制進行重新編譯為止。下列動作可能會導致預存程序計劃進行重新編譯：
  • 在 CREATE PROCEDURE 或 EXECUTE 陳述式中使用 WITH RECOMPILE 子句。
  • 任何參考物件的結構描述變更，包括加入或卸除條件約束、預設值或規則。
  • 為該程序所參考的資料表執行 sp_recompile。
  • (如果正在執行跨資料庫作業時) 還原包含該程序或該程序所參考任何物件的資料庫。
  • 快取中加入了足量的伺服器活動，導致該計劃過時而從中遭到排除。
這些都是舊版中存在重新編譯預存程序問題的原因，並導致該計劃必須先重新編譯才能開始執行程序。在 SQL Server 7.0 中則導入新的行為，可能會導致預存程序在執行期間重新編譯。這項新行為可以確保最佳化器對程序中每一個特定陳述式永遠都有可能的最佳計劃。下列事件可能會導致預存程序在執行階段重新編譯：
  • 由預存程序參考的資料表中的資料變更到達足夠百分比。
  • 該程序交錯進行資料定義語言 (Data Definition Language，DDL) 與資料操作語言 (Data Manipulation Language，DML) 作業。
  • 該程序在暫存資料表中執行特定作業。
上述每一種肇因都會在本文中進一步詳細地說明。

在某些情況下，重新編譯預存程序所耗費的成本會超過由這項操作產生的利益，尤其是大型程序。在這裡要提醒您非常重要的一點，觸發重新編譯時，會重新編譯整個批次或程序。也就是說，效能降低是與程序或批次的大小直接相關而成正比。如需有關此主題的詳細資訊，請參閱《SQL Server 線上叢書》中的「Transact-SQL 秘訣」主題。


本文中說明的下列資訊會將重心放在找出執行階段預存程序重新編譯的原因，並說明您可以用來預防的方法。

回此頁最上方

最佳做法

執行程序時，最好是限定擁有者權限預存程序名稱。如此更可以釐清程序的擁有者，也讓目前的使用者更加容易重新使用現有的執行計劃。例如，不具資料庫擁有者 (dbo) 身分的使用者要執行 pubs 資料庫中 dbo 擁有的預存程序 (在此範例中稱為 myProc)，請使用下列陳述式：
exec dbo.myProc
而不要使用下列陳述式：
exec myProc
從編碼及維護的觀點來看，這項技巧可以消除可能有不同擁有者之其他版本程序所造成的混淆，而且也讓 SQL Server 能夠更直接地存取特定程序的執行計劃。

如果不限定擁有者名稱，SQL Server 就會進入編譯程式碼，並針對該程序取得 COMPILE 鎖定。但是由於找不到符合的名稱，最後它會決定不需要新計劃 (假設沒有發生其他原因)，因此就「不會」在此時重新編譯計劃。不過，針對程序取得 COMPILE 鎖定的額外步驟可能會導致嚴重的鎖定爭用情況。請參閱 Q263889 Description of SQL Server blocking caused by compile locks，取得有關這種情況的詳細資訊。

如果您以「擁有者程序」的語法限定程序呼叫，就不需取得 COMPILE 鎖定，也因此能減少爭用情形。
回此頁最上方

找出及解決問題

如果您尚未參考知識庫，請參閱下列「Microsoft 知識庫」中的文件，取得有關擷取 Profiler 資料的詳細資訊，以協助分析系統的效能：
224587? (http://support.microsoft.com/kb/224587/ ) HOW TO：SQL Server 應用程式效能疑難排解

檢視 Profiler 資料

SQL Server Profiler 包含 SP:Recompile 事件，可供您用來監視發生重新編譯的次數。只要預存程序在執行期間重新編譯，就會發生 SP:Recompile 事件。
  • 依事件類別將 Profiler 追蹤分組

    1. 在 [檔案] 功能表上，按一下 [屬性]。
    2. 在 [資料行] 索引標籤上，使用 [向上] 按鈕移動 [群組] 標題下的 [事件類別] 和 [文字]，並且讓 [事件類別] 在前。使用 [向下] 按鈕，移除 [群組] 標題下的所有其他資料行。
    3. 按一下 [確定]。
    檢查 SP:Recompile 事件的數目。

    您可以展開 SP:Recompile 群組，查看個別發生情況的詳細資訊。此事件的 [文字] 資料行會指出經過重新編譯的預存程序名稱。如果有多個程序導致重新編譯，則依發生的次數排序。如果其中含有大量的 SP:Recompile 事件，而且 CPU 的使用率非常高，請將重心放在解決重新編譯次數最多的程序。請注意特定預存程序其中一個執行個體之 SP:Recompile 事件的系統處理序識別碼 (SPID) 和「開始時間」，並依照下列步驟執行。

    如果您沒看見任何 SP:Recompile 事件，但是仍然有效能上的問題，請參閱下列「Microsoft 知識庫」中的文件：
    224587? (http://support.microsoft.com/kb/224587/ ) HOW TO：SQL Server 應用程式效能疑難排解
  • 判斷觸發重新編譯事件的陳述式

    1. 在 [檔案] 功能表上，按一下 [屬性]。
    2. 在 [資料行] 索引標籤上，使用 [向下] 按鈕，移除 [群組] 標題下的所有其他資料行。
    3. 在 [事件] 索引標籤上，移除 SP:Starting、SP:StmtStarting、SP:Recompile 和 SP:Completed 以外的所有事件。如果沒有擷取 SP:StmtStarting 事件，則可以用 SP:StmtCompleted 取代，但不要兩者都包含，因為這會使需要查閱的資訊量加倍。
    4. 如果您已找出要檢查之預存程序重新編譯的特定執行個體，可以使用 [篩選] 索引標籤，將檢視的資料限制於發生該重新編譯的特定 SPID 和時段。
    5. 按一下 [確定]。

    SP:Recompile 事件會在導致重新編譯之預存程序陳述式的 SP:StmtStarted 事件之後立即引發。重新編譯事件完成以後，您會看到重複發生的 SP:StmtStarted 事件，表示陳述式是以新產生的計劃執行。

    請參閱下列範例：
    use pubs
go
drop procedure RecompProc 
go
create procedure RecompProc as
create table #t (a int)
select * from #t
go
exec RecompProc
    如果您在 Query Analyzer 中執行此程式碼，並且在 Profiler 追蹤中檢視上述事件，就會看到下列順序：

    摺疊此表格展開此表格
    事件類別文字
    SP:StartingRecompProc
    SP:StmtStartingcreate table #t (a int)
    SP:StmtStartingselect * from #t
    SP:RECOMPILERecompProc
    SP:StmtStartingselect * from #t
    SP:CompletedRecompProc


    立即可以看出導致重新編譯的陳述式是：
    select * from #t
    因為它在 SP:Recompile 事件之前和之後都出現。

    如果您只擷取 SP:StmtCompleted 事件，而未擷取 SP:StmtStarting 事件，則 SP:Recompile 將直接顯示在導致它發生的陳述式之前，如下所示：

    摺疊此表格展開此表格
    事件類別文字
    SP:StartingRecompProc
    SP:RECOMPILERecompProc
    SP:StmtCompletedselect * from #t
    SP:CompletedRecompProc


    您可以看到 SP:Recompile 事件是在 "select * from #t" 陳述式的 SP:StmtCompleted 事件之前引發，是它導致重新編譯。這樣解釋很合理，因為直到產生新的查詢計劃以便重新編譯之後，陳述式才能完成。本文中所有其他範例都會使用 SP:StmtStarting 事件。如果您只擷取 SP:StmtCompleted 事件，請記得如上述說明，檢視在 SP:Recompile 之後的陳述式。

    請注意，如果執行此特定預存程序多次，SQL Server 會重新使用此程序的現有計劃。您只會在初次執行程序上看到重新編譯事件，或是否每次執行指令碼，都是卸除後重新建立程序。在此特定情形下重新編譯的原因將會在本文的＜由於交錯進行資料定義語言 (DDL) 與資料操作語言 (DML) 作業而重新編譯＞一節中說明。這裡只是舉例說明如何簡單判斷是哪個陳述式導致重新編譯。

由於資料列修改而重新編譯

如果自產生原始查詢計劃以來，預存程序參考的資料表中已變更足夠百分比的資料，SQL Server 將會重新編譯預存程序，以確認具有以最新統計資料做為依據的計劃。請參閱下列預存程序範例：
drop procedure RowModifications 
go
create procedure RowModifications as
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
select count(*) from #t  where a = 37
go
exec RowModifications
exec RowModifications
第二次執行 RowModifications 程序時，您會在 Profiler 中看到下列事件：

摺疊此表格展開此表格
事件類別文字
SP:StartingRowModifications
SP:StmtStartingcreate table #t (a int, b char(10))
SP:StmtStartingselect * from #t
SP:StmtStartinginsert #t select * from SomeTable
SP:StmtStartingselect count(*) from #t where a = 37
SP:RECOMPILERowModifications
Auto-UpdateStatsa
SP:StmtStartingselect count(*) from #t where a = 37
SP:CompletedRowModifications

注意：第一次執行也會顯示 "select * from #t" 陳述式的 SP:Recompile 事件。在此特定情況下重新編譯的原因會在本文的＜由於交錯進行資料定義語言 (DDL) 與資料操作語言 (DML) 作業而重新編譯＞一節中說明。就此範例而言，請將重心放在以上所顯示的 SP:Recompile，因為它會在每次執行程序時發生。

在此範例中，由於變更了資料表建立以來的資料列數目，所以 "select count(*) from #t where a = 37" 會導致程序重新編譯。而出現 Auto-UpdateStats 事件，則是確認重新編譯是由於修改資料列的緣故。[文字] 資料行會指出已修改統計資料的資料行。

建立 #t 資料表時，資料列的數目是零。原始 "select * from #t" 的計劃以及 "select count (*)" 查詢的計劃都會隨著該資料列數量而發展。但是，在執行 "select count(*)" 之前，#t 資料表中已插入了 1,000 個新資料列。因為變更了足夠的資料量，最佳化器便會重新編譯程序，以確保選擇了對該陳述式最有效率的計劃。此重新編譯作業將在每次執行預存程序時發生，因為插入 1,000 個資料列會永遠被視為數量夠多，足以進行重新編譯。

SQL Server 用來判斷計劃是否應該重新編譯的演算法與用來自動更新統計資料的演算法相同，如下列「Microsoft 知識庫」中的文件所述：
195565? (http://support.microsoft.com/kb/195565/ ) Statistical maintenance functionality (autostats) in SQL Server
在上述範例中，由於預存程序很小，以至於看不到重新編譯對效能有任何明顯的影響。但如果是執行類似活動而造成多次重新編譯的大型預存程序，您就會注意到效能降低了。

下列方法可以抵制由於修改資料列而產生的重新編譯：
  • 使用 sp_executesql 執行陳述式。
    建議您採用這種方法。使用 sp_executesql 預存程序執行的陳述式不會編譯為預存程序計劃的一部分。因此執行陳述式時，SQL Server 將可自由地使用快取中現有的陳述式計劃，或是在執行階段建立新的計劃。不管任何一種情形，呼叫預存程序的計劃都不會受影響，也不必進行重新編譯。

    EXECUTE 陳述式將有相同的效果，但是不建議您使用。使用 EXECUTE 陳述式不如使用 sp_executesql 的效率高，因為它不允許查詢參數化。

    上文提供的 RowModifications 程序可以撰寫為使用 sp_executesql，如下所示：

    drop procedure RowModifications2 
go
create procedure RowModifications2 as
set nocount on
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
exec sp_executesql N'select count(*) from #t where a = @a', 
                   N'@a int', @a =  37
go
exec RowModifications2
exec RowModifications2

    第二次執行 RowModifications2 程序時，您會在 Profiler 中看到下列事件：

    摺疊此表格展開此表格
    事件類別文字
    SP:StartingRowModifications2
    SP:StmtStartingcreate table #t (a int, b char(10))
    SP:StmtStartingselect * from #t
    SP:StmtStartinginsert #t select * from SomeTable
    SP:StmtStartingexec sp_executesql N'select count(*) from #t where a = @a', N'@a int', @a = 37
    SP:Starting
    SP:StmtStartingselect count(*) from #t where a = @a
    Auto-UpdateStatsa
    SP:StmtStartingselect count(*) from #t where a = @a
    SP:Completed
    SP:CompletedRowModifications2


    請注意，RowModifications2 程序中並沒有任何 SP:Recompile 事件。sp_executesql 呼叫內容有從 SP:Starting 到 SP:Completed 的完整事件，以及資料行 a 的 Auto-UpdateStats 事件。但是由於此呼叫是在預存程序的內容以外，RowModifications2 程序不需要在此情況下重新編譯。

    如需有關使用 sp_executesql 預存程序的詳細資訊，請參閱《SQL Server 線上叢書》中的「sp_executesql (Transact-SQL)」及「使用 sp_executesql」主題。
  • 使用子程序，以執行導致重新編譯的陳述式。
    在此情況下，該陳述式可能還是會造成重新編譯，但是它不會重新編譯大型呼叫預存程序，而只會重新編譯小型子程序。
  • 使用 KEEP PLAN 選項。
    暫存資料表在重新編譯方面有特殊的規則，在某些情況下，可能會比預設重新編譯演算法更為嚴苛。您可以使用 KEEP PLAN 選項，放寬暫存資料表的臨界值，回復到預設演算法。如需詳細資訊，請參閱本文的＜使用 KEEP PLAN 選項＞一節。
注意：RowModifications 程序是非常簡化的範例，說明由於修改資料列而進行重新編譯的程序。請檢閱下列關於此範例的注意事項：

  • 雖然此範例是使用暫存資料表，但是這種情況也適用於參考永久資料表的預存程序。如果自產生查詢計劃以來，在參考的資料表中變更的資料量已足夠，預存程序將會重新進行編譯。在本文的＜使用 KEEP PLAN 選項以避免重新編譯＞一節中，將會說明暫存資料表在考慮進行重新編譯上有何不同。
  • 第一次執行上述兩個程序也會在第一次從暫存資料表 #t 進行選取時造成重新編譯，此原因將在本文的＜由於交錯進行資料定義語言 (DDL) 與資料操作語言 (DML) 作業而重新編譯＞一節中詳細說明。
  • 此範例會使用 "select count(*) from #t" 陳述式，而不是簡單的 "select * from #t" 陳述式。為了要避免過度重新編譯，SQL Server 不會考慮由於修改資料列而重新編譯「一般計劃」(例如從資料表選取 *)。

由於交錯進行資料定義語言 (DDL) 與資料操作語言 (DML) 作業而重新編譯

如果 DDL 作業是在某個程序或一批程序中執行，當該程序或批次遇到影響包含於該 DDL 中之資料表的第一個後續 DML 作業時，就會重新編譯。

請參閱下列範例預存程序：
drop procedure Interleave 
go
create procedure Interleave as
-- DDL
create table t1 (a int)
-- DML
select * from t1
-- DDL
create index idx_t1 on t1(a)
-- DML
select * from t1
-- DDL
create table t2 (a int)
-- DML
select * from t2
go
exec Interleave
如果您在 Query Analyzer 中執行此程式碼，並且在 Profiler 追蹤中檢視上述事件，就會看到下列順序：

摺疊此表格展開此表格
事件類別文字
SP:StartingInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingselect * from t1
SP:RECOMPILEInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingselect * from t1
SP:RECOMPILEInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t2
SP:RECOMPILEInterleave
SP:StmtStartingselect * from t2
SP:CompletedInterleave


在此範例中，預存程序在執行期間總共重新編譯了三次。如果要了解發生重新編譯的原因，請先了解最佳化器如何發展出此預存程序的計劃：
  1. 在程序初始編譯期間，資料表 t1 和 t2 並不存在。因此無法建立參考這些資料表之查詢的計劃，必須在執行期間產生。
  2. 初次執行程序時，第一個步驟是建立資料表 t1。下一個步驟是從資料表 t1 選取 -- 此時並沒有計劃。因此程序會在此時重新編譯，以發展計劃供 SELECT 陳述式使用。接著，便會產生計劃供目前從 t1 進行選取，也供建立索引之後，從 t1 進行選取。此時並沒有產生任何計劃可供從 t2 進行選取，因為 t2 還不存在。
  3. 下一個步驟是在 t1 上建立索引。接著在 t1 上執行另一個選取，該資料表現在已經有第一次重新編譯所產生的計劃。但是由於 t1 的結構描述從產生計劃以來已經有所變更，所以必須再度重新編譯程序，以便產生新的計劃可供從 t1 進行選取。而因為此時 t2 還不存在，所以並沒有產生任何計劃可供從 t2 進行選取。
  4. 接下來建立資料表 t2，並執行從 t2 進行選取。由於陳述式沒有任何計劃，程序便最後再重新編譯一次。
這些重新編譯作業都是在每一次執行預存程序時發生。如果要減少重新編譯次數，請將程序修改為先執行所有 DDL 作業，再接著執行 DML 作業，如下所示：
drop procedure NoInterleave 
go
create procedure NoInterleave as
-- All DDL first
create table t1 (a int)
create index idx_t1 on t1(a)
create table t2 (a int)
-- Then DML 
select * from t1
select * from t1
select * from t2
go
exec NoInterleave 
exec NoInterleave
第一次執行 NoInterleave 程序會在 Profiler 中顯示下列事件：

摺疊此表格展開此表格
事件類別文字
SP:StartingNoInterleave
SP:StmtStartingcreate table t1 (a int)
SP:StmtStartingcreate index idx_t1 on t1(a)
SP:StmtStartingcreate table t2 (a int)
SP:StmtStartingselect * from t1
SP:RECOMPILENoInterleave
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t1
SP:StmtStartingselect * from t2
SP:CompletedNoInterleave


此範例是先執行所有 DDL 陳述式，而最佳化器編譯此程序的流程如下所示：
  1. 在程序初始編譯期間，資料表 t1 和 t2 並不存在。因此無法建立參考這些資料表之查詢的計劃，必須在執行期間產生。
  2. 程序執行的最初幾個步驟包括 DDL 作業、建立資料表 t1 和 t2，以及在 t1 上建立索引。
  3. 下一個步驟是從 t1 進行第一次選取。由於並沒有任何計劃可供此 SELECT 陳述式使用，所以程序會重新編譯。因為所有物件都已存在，所以此時會為程序中所有 SELECT 陳述式產生計劃。
  4. 程序其餘部分都會使用所產生的計劃來執行。因為參考的物件沒有變更，所以不需要再進一步重新編譯程序。
注意：第二次及後續執行都會使用現有的查詢計劃和快取，因此完全不會導致任何重新編譯作業。您應該修改會建立、更改或卸除資料表的程序，以確保所有 DDL 陳述式都位於程序開頭。

由於建立暫存資料表作業而重新編譯

在預存程序中使用暫存資料表可能會導致預存程序在每一次執行時都要重新編譯。

如果要避免此情形，請變更預存程序，以符合下列需求：
  • 包含暫存資料表名稱的所有陳述式都會參考相同預存程序中建立的暫存資料表，而不是參考呼叫或被呼叫預存程序中的資料表，或是使用 EXECUTE 陳述式或 sp_executesql 預存程序所執行字串中的資料表。
  • 在句法上，包含暫存資料表名稱的所有陳述式都會出現在預存程序或觸發程序中的暫存資料表之後。
  • 沒有任何 DECLARE CURSOR 陳述式的 SELECT 陳述式會參考暫存資料表。
  • 包含任何暫存資料表名稱的所有陳述式，都會在任何參考暫存資料表的 DROP TABLE 陳述式之前。

    在預存程序中建立的暫存資料表不需要 DROP TABLE 陳述式，資料表會在程序完成後自動卸除。
  • 沒有任何建立暫存資料表的陳述式 (如 CREATE TABLE 或 SELECT...INTO) 會出現在控制流程的陳述式中，如 IF...ELSE 或 WHILE。

使用 KEEP PLAN 選項避免重新編譯

在預存程序中使用暫存資料表會為查詢最佳化器導入一些複雜性。資料表的資料列個數和統計資訊在預存程序執行的整個存留期間會有極劇烈的變化。為了確保最佳化工具在所有與暫存資料表相關的所有情況中都使用最佳計劃，而發展出更積極執行重新編譯的特殊演算法。此演算法說明如果利用預存程序建立的暫存資料表變更超過六次，程序將在下一個陳述式參考暫存資料表時重新編譯。

請參閱下列範例：
drop procedure useKeepPlan 
go
create procedure useKeepPlan as
create table #t (a int, b char(3))
select * from #t
-- Make greater than 6 changes to #t
insert #t values (1, 'abc')
insert #t values (2, 'abc')
insert #t values (3, 'abc')
insert #t values (4, 'abc')
insert #t values (5, 'abc')
insert #t values (6, 'abc')
insert #t values (7, 'abc')
-- Now reference #t
select count(*) from #t 
--option (KEEP PLAN)
go
exec useKeepPlan
exec useKeepPlan
在此範例中，您會在第二次執行程序時，在 Profiler 中看到下列事件：

摺疊此表格展開此表格
事件類別文字
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting - 第七個插入的陳述式 -
SP:StmtStartingselect count(*) from #t1
SP:RECOMPILEuseKeepPlan
SP:StmtStartingselect count(*) from #t1
SP:CompleteduseKeepPlan

程序會在暫存資料表 #t 第七次變更之後進行選取時重新編譯。

在暫存資料表的資料散發變更會對參考資料表之陳述式的最佳化查詢計劃有劇烈影響的情形下，這種積極的重新編譯很有幫助。但是對於經常修改暫存資料表而沒有重大影響的大型程序，重新編譯可能會造成整體效能遲緩。SELECT 陳述式的 KEEP PLAN 選項就是特別針對此情況而導入。

KEEP PLAN 會清除程序中變更暫存資料表六次以上所造成的預存程序重新編譯，還原到本文的＜由於資料列修改而重新編譯＞一節中所說明由於資料列修改而重新編譯的標準演算法。KEEP PLAN 不會完全阻止重新編譯，而只是阻止程序中所參考暫存資料表變更超過六次以上所造成的重新編譯。針對上述範例，如果從預存程序中移除 "option (KEEP PLAN)" 行的註解，就不會產生 SP:Recompile 事件。

如果從上述程式碼中的 "option (KEEP PLAN)" 行移除註解，然後執行此程式碼，就會在 Profiler 中看到下列事件：

摺疊此表格展開此表格
事件類別文字
SP:StartinguseKeepPlan
SP:StmtStartingcreate table #t (a int)
SP:StmtStarting - 第七個插入的陳述式 -
SP:StmtStartingselect count(*) from #t1 option (KEEP PLAN)
SP:CompleteduseKeepPlan


請注意，其中並沒有 SP:Recompile 事件。
回此頁最上方

由於預存程序中執行的某個 SET 陳述式而重新編譯

下列五個 SET 選項預設設定為 ON：
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
如果執行 SET 陳述式，將其中任何一個選項設定為 OFF，預存程序就會在每次執行時重新編譯。其原因在於變更這些選項可能會影響查詢結果而觸發重新編譯。

請參閱下列範例程式碼：
Use pubs
drop procedure test_recompile
go

create procedure test_recompile as
Set ANSI_DEFAULTS OFF
Select au_lname, au_fname, au_id from authors
where au_lname like 'L%'
--Option (Keep Plan)
Go
在此範例中，每次執行預存程序時，您都會在 SQL Profiler 中看到下列事件：
+---------------------------------------------------+
| 事件類別        | 文字                            | 
+---------------------------------------------------+
| SP:Starting     | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | Set ANSI_DEFAULTS OFF           | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Recompile    | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Completed    | test_recompile                  | 
+---------------------------------------------------+
以上面所列五個選項中的任何一個取代 SET 選項都會顯示相同的結果。而且，在此處使用 KEEP PLAN 選項無助於避免重新編譯，因為造成重新編譯的原因是來自 SET 陳述式。

建議避免重新編譯的方式，就是不要在預存程序中使用這五個之中的任何一個 SET 陳述式。如需其他資訊，請參閱下列「Microsoft 知識庫」中的文件：
294942? (http://support.microsoft.com/kb/294942/ ) PRB: SET CONCAT_NULL_YIELDS_NULL May Cause Stored Procedures to Recompile
執行 SET 陳述，重設與預存程序相同值的連線選項，也可以避免重新編譯，但是並不建議使用這種方式，其作法如下所示：
Set ANSI_DEFAULTS OFF

exec test_recompile
如此 SQL Profiler 追蹤將不會再顯示任何 SP:Recompile 事件。

下表列出一些常見 SET 陳述式，以及在預存程序中變更 SET 陳述式是否會導致重新編譯：
摺疊此表格展開此表格
Set 陳述式重新編譯
Set quoted_identifier否
Set arithabort是
Set ansi_null_dflt_on是
Set ansi_defaults是
Set ansi_warnings是
Set ansi_padding是
Set concat_null_yields_null是
Set numeric_roundabort否
Set nocount否
Set rowcount否
Set xact_abort否
Set implicit_transactions否
Set arithignore否
Set lock_timeout否
Set fmtonly否

回此頁最上方

參考

308737? (http://support.microsoft.com/kb/308737/ ) How to identify the cause of recompilation in an SP:Recompile event

如需有關使用 SQL Server Profiler 的資訊，請參閱《SQL Server 線上叢書》。
回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
回此頁最上方
關鍵字：?
kbinfo KB243586
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。