SQL Prompt使用教程：更改過程或觸發器中的SET選項將導致重新編譯（下）

---

SQL Prompt根據數據庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀--當開發者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。

本教程介紹了SQL Prompt的性能規則PE012，該規則將建議您是否在存儲過程或觸發器中檢測到SET語句的使用，這可能會導致不必要的重新編譯，盡管問題涉及其他類型的批處理。教程內容較多，分為上下兩篇文章，本文是后半部分，內容緊接前文，想要查看前文請點擊這里！

點擊下載SQL Prompt正式版

---

在存儲過程中更改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一起使用來促進代碼重用，或者在應用程序中，我們正確地使用綁定參數。為了謹慎起見，我們使用表變量。

本教程內容到這里就完結了，由于教程內容較多，分為上下兩篇文章，您可以點擊這里查看~您也可以下載SQL Prompt試用版免費體驗~

相關內容推薦：

SQL Prompt使用教程：更改過程或觸發器中的SET選項將導致重新編譯（上）

---

想要購買SQL Prompt正版授權，或了解更多產品信息請點擊