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

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

點(diǎn)擊下載SQL Prompt正式版

在存儲(chǔ)過程中更改SET選項(xiàng)

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

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

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

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

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

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

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

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

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

我們優(yōu)先使用存儲(chǔ)過程和觸發(fā)器來處理動(dòng)態(tài)Transact-SQL批處理，因?yàn)樗鼈兏子谥赜谩Ｋ鼈兪菂?shù)化的，因此SQL文本永不更改，從而促進(jìn)了重用。在準(zhǔn)備好的批次或過程中更改設(shè)置時(shí)，設(shè)置選項(xiàng)僅用于執(zhí)行準(zhǔn)備好的批次或過程，

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

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

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

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

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

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

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

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

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

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

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

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

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