更改過程或觸發器中的SET選項將導致重新編譯

SQL Prompt根據數據庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀--當開發者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。本教程介紹了SQL Prompt的性能規則PE012，該規則將建議您是否在存儲過程或觸發器中檢測到SET語句的使用，這可能會導致不必要的重新編譯，盡管問題涉及其他類型的批處理。

有時，由于某種顯而易見的原因，您將有一個存儲過程或觸發器間歇地花費更長的時間運行。您已經檢查了索引，排除了諸如參數嗅探之類的問題，但是間歇性的性能問題仍然存在。SET為了更改執行設置，是否可以像您在批處理中發出語句那樣簡單呢？如果這樣做，則可能是由于SQL Server需要重新編譯該過程或重復觸發而導致了該問題。

重新編譯沒有什么特別的錯誤，實際上，強制執行某些查詢在每次執行時重新編譯是很常見的，正是為了避免與參數嗅探、濫用Execute()或包羅萬象的查詢有關的不良性能問題。但是，如果重新編譯變得過多，尤其是對于頻繁或昂貴的查詢，則可能會成為問題，值得調查原因，我將向您展示如何使用擴展事件。

什么是重新編譯？

當SQL Server執行臨時批處理或查詢或諸如存儲過程或觸發器之類的對象時，SQL Server將為每個批處理或對象以及該批處理或對象中的每個查詢編譯針對當前狀態進行優化的執行計劃數據庫，其對象及其數據。SQL Server的優化器設計此計劃需要花費時間和資源，但是必須在代碼可以傳遞到執行引擎之前完成。幸運的是，我們傾向于重復執行相同的查詢或過程，可能使用不同的參數，因此SQL Server將其生成的大多數計劃存儲在計劃緩存中，并且無論我們使用什么參數值，都將確保所有計劃都可以安全地重用。當我們再次執行相同的批處理或對象時，只要有可能，它將簡單地重用其緩存的計劃。

但是，有時我們會重新執行存儲過程，或者重新提交批處理或查詢優化器之前已見過的緩存，并且針對該優化器在緩存中具有優化的計劃，但是由于某些原因，它無法重用該計劃并編譯一個新的。這是重新編譯，并且由于各種原因而發生。如果執行引擎檢測到表已更改或其統計信息已發生重大變化，它將自動發生，這時它將標記要重新編譯訪問該表的查詢的所有緩存計劃。下次運行其中一個查詢時，優化器將生成新計劃，而舊計劃將被刪除。

我們還可以通過將OPTION (RECOMPILE)提示附加到查詢來強制優化器不斷重新編譯計劃。該查詢的計劃可能仍在高速緩存中，但不會被重用。通常這樣做是為了處理由于參數嗅探，使用“catch-all”過程，濫用Execute()等等所導致的不穩定性能。

為了節省時間和資源，SQL Server會在可能的情況下進行語句級的重新編譯。如果批處理或存儲過程中僅一個語句的計劃因數據結構或數據的基礎更改而無效，或者只有一個語句具有OPTION (RECOMPILE)提示，則僅重新編譯受影響的語句的計劃，而不重新編譯整個批處理或存儲。

有時，重新編譯既不會因數據結構或數據的更改而自動觸發，也不會由于使用提示而被強制執行。我們在同一數據庫上重新執行相同的查詢，存在一個匹配的緩存計劃，因為提交的查詢的SQL文本和與該緩存計劃相關聯的SQL文本完全匹配（包括空格和回車符），但是該計劃沒有被重用。

再次，有幾種可能的原因，我們將不在這里進一步討論，例如，對未在過程中靜態創建的臨時表的引用，或者缺少模式驗證，而我們將要解決的原因是緩存的計劃是使用與提交查詢的連接所使用的SET選項不同的SET選項創建的。

“影響計劃重用”的SET選項

更改某些SET選項的值（有時稱為“影響計劃重用”的選項）將更改查詢的運行方式及其結果。因此，當優化器檢查其緩存計劃是否匹配時，它包括檢查在編譯緩存計劃中使用的SET選項是否與發布批次的連接中使用的SET選項匹配。如果它們不匹配，則它將不會重復使用現有計劃，而是會編譯一個新計劃。

這意味著您可以看到多個緩存的計劃，除了這些SET選項的細節外，它們基本上是相同的。

這些“計劃重用影響”選項，按字母順序排列，ANSI_DEFAULTS、ANSI_NULL_DFLT_OFF、ANSI_NULL_DFLT_ON、ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、CONCAT_NULL_YIELDS_NULL、DATEFIRST、DATEFORMAT、FORCEPLAN、LANGUAGE、NO_BROWSETABLE、NUMERIC_ROUNDABORT和QUOTED_IDENTIFIER。

當SQL Server在編譯過程中執行“恒定折疊”時，會檢測到這些SET語句，并且似乎在舊版本的SQL Server中，每次調用該過程時，將其中某些SET選項更改為某些值可能會導致重新編譯。但是，在最新版本的SQL Server中，很少聽到此問題。

但是，明智的改變是SET選項，在批處理開始時，甚至在觸發器過程內更改選項，可以導致編譯新計劃，只有在執行完全相同的批處理或對象，具有完全相同的設置時，才可以重新使用該計劃。雖然以這種方式重新編譯計劃很少會引起主要的性能問題，但確實會帶來CPU成本，并且可能會引起問題，尤其是對于編譯成本高且執行頻率高的復雜查詢，甚至可能同時出現這兩種情況在多語句程序中。

更改連接設置

對于ODBC、ADO或JDBC連接，為連接的默認設置指定任何更改的方法是，在首次建立連接后執行初步的SET語句批處理。連接字符串中沒有允許該操作的選項：必須由SET語句完成。在SSMS中，您可以使用“查詢”菜單（“查詢” >“查詢選項”）為連接的執行行為指定高級和ANSI標準選項。在進行開發和測試時，值得將它們設置為與生產系統連接所使用的相同。這些設置僅反映建立連接時的執行設置。如果隨后在連接中的批次中更改設置，則這些設置將用于后續批次。

您會注意到，此選項卡（和ANSI選項卡，沒有顯示）中的SET選項沒有涵蓋所有“計劃-重用-影響”選項。其余的操作必須在通過SET選項語句建立新連接時完成。

通過更改SET選項更改結果

如前所述，會話SET選項的更改在某些情況下可能導致錯誤或警告，或者導致查詢的結果不同。快速演示值得一提，在這里，我將在每批開始時簡單地更改幾個SET選項的值：

在ARITHABORT設置為ON的情況下，查詢遇到0除時，查詢將以錯誤（我們捕獲到這個錯誤）結束，因此返回2行。當我們關閉此選項時，同一查詢將返回3行：

如果檢查每個批次的計劃，除了這些SET選項的值（打開SELECT操作符的屬性以查看它們）之外，您將看到它們是相同的：

以下查詢將向我們展示計劃緩存中的情況（我已經在PhilFactor數據庫中完成了此操作，因此您需要進行更改）。

得到這個結果…

由于SET選項設置不同（235和4331），每個批次都有自己的編譯計劃。您會注意到，該計劃的一個屬性set_options，為您提供了所有SET選項的位圖值，其中大多數選項為on或off。

每次更改這些設置選項中的一個時，您都會看到專門為該選項集創建的新計劃，這顯然會增加對緩存的要求以及編譯計劃所花費的CPU時間。如果您對這兩個批次執行十次，您將看到使用了適當的計劃，而無需重新編譯。

在存儲過程中更改SET選項

到目前為止，我們僅處理批處理，但是如果由于某種原因要確保使用特定設置執行各個過程該怎么辦？

我已經將相同的邏輯封裝在三個存儲過程中，前兩個對我們的兩個選項使用了特定的設置，而第三個沒有任何SET選項語句。

我對這三個過程分別執行了兩次，首先是在所有選項均使用“默認”設置的會話中進行，其中ARITHABORT和ANSI_WARNINGS均處于ON狀態（set_options = 4347），然后從前者處于ON狀態而后者處于OFF狀態的會話中（4331），最后從兩個都關閉的會話中（235）。

我們總共看到9個計劃，每次從具有不同set_options值的連接執行該計劃時，都會為每個過程編譯一個新計劃。換句話說，如果調用批處理的執行設置與編譯該過程的任何執行計劃時有效的執行設置不匹配，則會使用新的set選項創建一個新的緩存計劃。如果我們使用連接相同set_options值重新執行相同的存儲過程，則該計劃將被重用。

調用第一個存儲過程（顯式設置ARITHABORT為ON）始終返回2行，而調用第二個存儲過程始終返回3行。在不使用SET語句的情況下調用過程時，它僅取決于調用連接的設置。

如果您更改了過程中的設置，則它們僅在該過程中有效，因此它們不會影響調用該過程的批處理。所有9個計劃都顯示了用于執行調用批處理的連接的SET選項值。

在過程和觸發器中捕捉“影響計劃的重用”的SET語句的使用

SQL Prompt中的性能規則（PE012）看起來是否SET在存儲過程和觸發器（盡管不是批處理）中做出了任何“影響計劃重用”的SET語句。您還可以使用SQL Change Automation運行檢查，以在數據庫構建源中發現此問題。SQL Monitor還支持代碼分析。

不過請注意：這種現象不僅適用于過程或觸發器，而且還適用于任何臨時批處理、使用sp_executesql執行的批、準備好的查詢和動態SQL。如果發出“影響計劃重用”SET語句，則對于其中任何一個的緩存計劃都無法如此輕松地重用，并且在SQL Server的早期版本中，每次使用都會有重新編譯的風險。

我們優先使用存儲過程和觸發器來處理動態Transact-SQL批處理，因為它們更易于重用。它們是參數化的，因此SQL文本永不更改，從而促進了重用。在準備好的批次或過程中更改設置時，設置選項僅用于執行準備好的批次或過程，

批處理也可以重用，但是如果通過sp_executesql或Prepare方法（而不是動態SQL或Execute方法）執行批處理，SQL Server發現這樣做更容易。

更糟糕的是，在執行臨時批處理時，SET選項中的任何更改都會從該批處理中泄漏出來，從而使連接保留其新設置：您必須顯式還原設置，但是在該點之前立即中止該批處理的錯誤，將無法執行代碼。然后，優化器可能需要編譯新計劃，以針對您在該連接上執行的所有后續批處理和過程的這些新設置。

很難檢測到此錯誤，它增強了以下一般建議：在建立連接后，這些語句必須始終作為初步批處理執行，并且隨后避免任何更改。這意味著所有此類SET語句在代碼中都是可疑的，應被視為“SQL代碼氣味”。很難證明它們的合理性。

調查過度重新編譯

在擴展事件不可用或過于粗糙的SQL Server版本中，可以使用SQL Server Profiler。盡管SP：Recompile跟蹤事件可以僅用于報告過程和觸發器的語句級重新編譯，但SQL：StmtRecompile也可以用于跟蹤和調試重新編譯，它可以檢測存儲過程，觸發器，臨時批處理的重新編譯，使用sp_executesql，準備好的查詢和動態SQL執行的批處理。SP:Recompile和SQL:StmtRecompile的event子類列包含一個整數代碼，指出重新編譯的原因。

通過擴展事件，事情變得更加文明。我們可以獲得有關重新編譯及其原因的完整報告。這是一個簡單的會話，用于報告各個編譯。

這樣，我們可以獲得單個重新編譯的詳細信息。我通常在sqlserver.username字段上添加會話事件過濾器，以僅針對特定用戶（運行測試代碼的測試用戶的名稱）獲得重新編譯。否則會產生很多噪音。

總結

如果您發現代碼中包含涉及“計劃重用影響”選項的SET語句，那么這就是代碼的味道，您應該調查原因。

您當然可以做一些狡猾而聰明的事情，但是在我從事SQL Server開發的工作中，我從未發現過。這不僅是存儲過程或觸發器中的不良做法，而且還可能以任何批次執行多次。如果需要設置語言、ANSI選項或錯誤處理兼容性，則在創建連接并創建單個標準時進行設置。如果這樣做失敗，則會導致SQL Server執行不必要的重新編譯。

當我寫這些SET語句的使用是“不好的”時，我并不希望暗示批處理的重新編譯一定是不好的：有時它們避免了一些隱匿的性能問題之一，并且它們很少會影響性能只要不沉迷于SQL代碼，應用程序的氣味就不必要了。例如，當我們創建要重用的批處理時，我們總是通過與參數sp_ExecuteSQL一起使用來促進代碼重用，或者在應用程序中，我們正確地使用綁定參數。為了謹慎起見，我們使用表變量。