共用方式為


重新編譯預存程序

若由於新增索引或變更索引資料行中的資料等動作,導致資料庫變更的話,就應該重新編譯用來存取資料表的原始查詢計畫,將其再次最佳化。此最佳化會在重新啟動 SQL Server 後、首次執行預存程序時自動發生。若預存程序所使用的基礎資料表有變更,也會發生最佳化。但如果加入新的索引,雖然預存程序可能受益,但在下一次重新啟動 SQL Server 後執行預存程序之前,將不會發生最佳化。在這樣的情況下,強制預存程序在下次執行時重新編譯將非常有用。

另一個強制預存程序重新編譯的理由為,在必要時可抵制預存程序編譯的「參數探查」行為。當 SQL Server 執行預存程序時,程序在編譯時所使用的參數值將包含在產生查詢計畫的一部分中。如果這些值代表程序後續呼叫的典型值,則預存程序在每次編譯和執行時都會因為查詢計畫而獲益。如果沒有,效能就可能會降低。

SQL Server 2008 R2 的特色功能在於預存程序的陳述式層級重新編譯。當 SQL Server 2008 R2 重新編譯預存程序時,只會編譯造成重新編譯的陳述式,而不是整個程序。因此,SQL Server 在重新產生查詢計畫時,會在重新編譯的陳述式中使用參數值。這些值可能會與原始傳遞至程序的值不同。

強制預存程序重新編譯

SQL Server 提供三種強制預存程序重新編譯的方法:

  • sp_recompile 系統預存程序會強制在下一次執行預存程序時重新編譯。其運作方式是從程序快取中刪除現有的計畫,以便強制在下次執行程序時建立新的計畫。

  • 建立預存程序時,在它的定義中指定 WITH RECOMPILE 選項,指示 SQL Server 不要快取這個預存程序的計畫;每次執行這個預存程序時都會重新編譯。若每次執行預存程序時所接受的參數值差異都極大,就可以使用 WITH RECOMPILE 選項,以便每次建立不同的執行計畫。這個選項並不常用,也會使預存程序的執行速度變慢,因為每次執行預存程序時都必須重新編譯。

    如果您只希望重新編譯預存程序內的個別查詢,而不是整個預存程序,請指定您要重新編譯的每個查詢內之 RECOMPILE 查詢提示。此行為是模擬本節前面所提及的 SQL Server 陳述式層級的重新編譯行為,但是除了使用預存程序目前的參數值之外,RECOMPILE 查詢提示也會在編譯陳述式時,使用預存程序內任何區域變數的值。當只有預存程序所屬的查詢子集使用非典型值或暫存值時,請使用此選項。如需詳細資訊,請參閱<查詢提示 (Transact-SQL)>。

  • 執行預存程序時指定 WITH RECOMPILE 選項,就可以強制重新編譯預存程序。如果提供的參數不太正常,或是在建立預存程序後資料已發生顯著的變更,就可以使用這個選項。

    [!附註]

    如果預存程序所參考的物件已被刪除或重新命名,執行預存程序時就會傳回錯誤。但如果以同名物件取代預存程序中所參考的物件,預存程序就會執行,而不須重新建立。

若要在下一次執行預存程序時重新編譯