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

---

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

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

點擊下載SQL Prompt正式版

---

在存儲過程中更改SET選項

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

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

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

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

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

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

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

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

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

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

批處理也可以重用，但是如果通過sp_executesql或Prepare方法（而不是動態(tài)SQL或Execute方法）執(zhí)行批處理，SQL Server發(fā)現(xiàn)這樣做更容易。

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

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

調(diào)查過度重新編譯

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

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

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

總結(jié)

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

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

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

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

相關(guān)內(nèi)容推薦：

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

---

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點擊【咨詢在線客服】