更改過(guò)程或觸發(fā)器中的SET選項(xiàng)將導(dǎo)致重新編譯-創(chuàng)新互聯(lián)

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

站在用戶的角度思考問(wèn)題，與客戶深入溝通，找到合江網(wǎng)站設(shè)計(jì)與合江網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：做網(wǎng)站、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、主機(jī)域名、網(wǎng)頁(yè)空間、企業(yè)郵箱。業(yè)務(wù)覆蓋合江地區(qū)。

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

重新編譯沒(méi)有什么特別的錯(cuò)誤，實(shí)際上，強(qiáng)制執(zhí)行某些查詢?cè)诿看螆?zhí)行時(shí)重新編譯是很常見的，正是為了避免與參數(shù)嗅探、濫用Execute()或包羅萬(wàn)象的查詢有關(guān)的不良性能問(wèn)題。但是，如果重新編譯變得過(guò)多，尤其是對(duì)于頻繁或昂貴的查詢，則可能會(huì)成為問(wèn)題，值得調(diào)查原因，我將向您展示如何使用擴(kuò)展事件。

什么是重新編譯？

當(dāng)SQL Server執(zhí)行臨時(shí)批處理或查詢或諸如存儲(chǔ)過(guò)程或觸發(fā)器之類的對(duì)象時(shí)，SQL Server將為每個(gè)批處理或?qū)ο笠约霸撆幚砘驅(qū)ο笾械拿總€(gè)查詢編譯針對(duì)當(dāng)前狀態(tài)進(jìn)行優(yōu)化的執(zhí)行計(jì)劃數(shù)據(jù)庫(kù)，其對(duì)象及其數(shù)據(jù)。SQL Server的優(yōu)化器設(shè)計(jì)此計(jì)劃需要花費(fèi)時(shí)間和資源，但是必須在代碼可以傳遞到執(zhí)行引擎之前完成。幸運(yùn)的是，我們傾向于重復(fù)執(zhí)行相同的查詢或過(guò)程，可能使用不同的參數(shù)，因此SQL Server將其生成的大多數(shù)計(jì)劃存儲(chǔ)在計(jì)劃緩存中，并且無(wú)論我們使用什么參數(shù)值，都將確保所有計(jì)劃都可以安全地重用。當(dāng)我們?cè)俅螆?zhí)行相同的批處理或?qū)ο髸r(shí)，只要有可能，它將簡(jiǎn)單地重用其緩存的計(jì)劃。

但是，有時(shí)我們會(huì)重新執(zhí)行存儲(chǔ)過(guò)程，或者重新提交批處理或查詢優(yōu)化器之前已見過(guò)的緩存，并且針對(duì)該優(yōu)化器在緩存中具有優(yōu)化的計(jì)劃，但是由于某些原因，它無(wú)法重用該計(jì)劃并編譯一個(gè)新的。這是重新編譯，并且由于各種原因而發(fā)生。如果執(zhí)行引擎檢測(cè)到表已更改或其統(tǒng)計(jì)信息已發(fā)生重大變化，它將自動(dòng)發(fā)生，這時(shí)它將標(biāo)記要重新編譯訪問(wèn)該表的查詢的所有緩存計(jì)劃。下次運(yùn)行其中一個(gè)查詢時(shí)，優(yōu)化器將生成新計(jì)劃，而舊計(jì)劃將被刪除。

我們還可以通過(guò)將OPTION (RECOMPILE)提示附加到查詢來(lái)強(qiáng)制優(yōu)化器不斷重新編譯計(jì)劃。該查詢的計(jì)劃可能仍在高速緩存中，但不會(huì)被重用。通常這樣做是為了處理由于參數(shù)嗅探，使用“catch-all”過(guò)程，濫用Execute()等等所導(dǎo)致的不穩(wěn)定性能。

為了節(jié)省時(shí)間和資源，SQL Server會(huì)在可能的情況下進(jìn)行語(yǔ)句級(jí)的重新編譯。如果批處理或存儲(chǔ)過(guò)程中僅一個(gè)語(yǔ)句的計(jì)劃因數(shù)據(jù)結(jié)構(gòu)或數(shù)據(jù)的基礎(chǔ)更改而無(wú)效，或者只有一個(gè)語(yǔ)句具有OPTION (RECOMPILE)提示，則僅重新編譯受影響的語(yǔ)句的計(jì)劃，而不重新編譯整個(gè)批處理或存儲(chǔ)。

有時(shí)，重新編譯既不會(huì)因數(shù)據(jù)結(jié)構(gòu)或數(shù)據(jù)的更改而自動(dòng)觸發(fā)，也不會(huì)由于使用提示而被強(qiáng)制執(zhí)行。我們?cè)谕粩?shù)據(jù)庫(kù)上重新執(zhí)行相同的查詢，存在一個(gè)匹配的緩存計(jì)劃，因?yàn)樘峤坏牟樵兊腟QL文本和與該緩存計(jì)劃相關(guān)聯(lián)的SQL文本完全匹配（包括空格和回車符），但是該計(jì)劃沒(méi)有被重用。

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

“影響計(jì)劃重用”的SET選項(xiàng)

更改某些SET選項(xiàng)的值（有時(shí)稱為“影響計(jì)劃重用”的選項(xiàng)）將更改查詢的運(yùn)行方式及其結(jié)果。因此，當(dāng)優(yōu)化器檢查其緩存計(jì)劃是否匹配時(shí)，它包括檢查在編譯緩存計(jì)劃中使用的SET選項(xiàng)是否與發(fā)布批次的連接中使用的SET選項(xiàng)匹配。如果它們不匹配，則它將不會(huì)重復(fù)使用現(xiàn)有計(jì)劃，而是會(huì)編譯一個(gè)新計(jì)劃。

這意味著您可以看到多個(gè)緩存的計(jì)劃，除了這些SET選項(xiàng)的細(xì)節(jié)外，它們基本上是相同的。

這些“計(jì)劃重用影響”選項(xiàng)，按字母順序排列，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。

當(dāng)SQL Server在編譯過(guò)程中執(zhí)行“恒定折疊”時(shí)，會(huì)檢測(cè)到這些SET語(yǔ)句，并且似乎在舊版本的SQL Server中，每次調(diào)用該過(guò)程時(shí)，將其中某些SET選項(xiàng)更改為某些值可能會(huì)導(dǎo)致重新編譯。但是，在最新版本的SQL Server中，很少聽到此問(wèn)題。

但是，明智的改變是SET選項(xiàng)，在批處理開始時(shí)，甚至在觸發(fā)器過(guò)程內(nèi)更改選項(xiàng)，可以導(dǎo)致編譯新計(jì)劃，只有在執(zhí)行完全相同的批處理或?qū)ο螅哂型耆嗤脑O(shè)置時(shí)，才可以重新使用該計(jì)劃。雖然以這種方式重新編譯計(jì)劃很少會(huì)引起主要的性能問(wèn)題，但確實(shí)會(huì)帶來(lái)CPU成本，并且可能會(huì)引起問(wèn)題，尤其是對(duì)于編譯成本高且執(zhí)行頻率高的復(fù)雜查詢，甚至可能同時(shí)出現(xiàn)這兩種情況在多語(yǔ)句程序中。

更改連接設(shè)置

對(duì)于ODBC、ADO或JDBC連接，為連接的默認(rèn)設(shè)置指定任何更改的方法是，在首次建立連接后執(zhí)行初步的SET語(yǔ)句批處理。連接字符串中沒(méi)有允許該操作的選項(xiàng)：必須由SET語(yǔ)句完成。在SSMS中，您可以使用“查詢”菜單（“查詢” >“查詢選項(xiàng)”）為連接的執(zhí)行行為指定高級(jí)和ANSI標(biāo)準(zhǔn)選項(xiàng)。在進(jìn)行開發(fā)和測(cè)試時(shí)，值得將它們?cè)O(shè)置為與生產(chǎn)系統(tǒng)連接所使用的相同。這些設(shè)置僅反映建立連接時(shí)的執(zhí)行設(shè)置。如果隨后在連接中的批次中更改設(shè)置，則這些設(shè)置將用于后續(xù)批次。

您會(huì)注意到，此選項(xiàng)卡（和ANSI選項(xiàng)卡，沒(méi)有顯示）中的SET選項(xiàng)沒(méi)有涵蓋所有“計(jì)劃-重用-影響”選項(xiàng)。其余的操作必須在通過(guò)SET選項(xiàng)語(yǔ)句建立新連接時(shí)完成。

通過(guò)更改SET選項(xiàng)更改結(jié)果

如前所述，會(huì)話SET選項(xiàng)的更改在某些情況下可能導(dǎo)致錯(cuò)誤或警告，或者導(dǎo)致查詢的結(jié)果不同?？焖傺菔局档靡惶?，在這里，我將在每批開始時(shí)簡(jiǎn)單地更改幾個(gè)SET選項(xiàng)的值：

在ARITHABORT設(shè)置為ON的情況下，查詢遇到0除時(shí)，查詢將以錯(cuò)誤（我們捕獲到這個(gè)錯(cuò)誤）結(jié)束，因此返回2行。當(dāng)我們關(guān)閉此選項(xiàng)時(shí)，同一查詢將返回3行：

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

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

得到這個(gè)結(jié)果…

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

每次更改這些設(shè)置選項(xiàng)中的一個(gè)時(shí)，您都會(huì)看到專門為該選項(xiàng)集創(chuàng)建的新計(jì)劃，這顯然會(huì)增加對(duì)緩存的要求以及編譯計(jì)劃所花費(fèi)的CPU時(shí)間。如果您對(duì)這兩個(gè)批次執(zhí)行十次，您將看到使用了適當(dāng)?shù)挠?jì)劃，而無(wú)需重新編譯。

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

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

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

我對(duì)這三個(gè)過(guò)程分別執(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è)過(guò)程編譯一個(gè)新計(jì)劃。換句話說(shuō)，如果調(diào)用批處理的執(zhí)行設(shè)置與編譯該過(guò)程的任何執(zhí)行計(jì)劃時(shí)有效的執(zhí)行設(shè)置不匹配，則會(huì)使用新的set選項(xiàng)創(chuàng)建一個(gè)新的緩存計(jì)劃。如果我們使用連接相同set_options值重新執(zhí)行相同的存儲(chǔ)過(guò)程，則該計(jì)劃將被重用。

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

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

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

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

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

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

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

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

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

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

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

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

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

總結(jié)

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

您當(dāng)然可以做一些狡猾而聰明的事情，但是在我從事SQL Server開發(fā)的工作中，我從未發(fā)現(xiàn)過(guò)。這不僅是存儲(chǔ)過(guò)程或觸發(fā)器中的不良做法，而且還可能以任何批次執(zhí)行多次。如果需要設(shè)置語(yǔ)言、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語(yǔ)句的使用是“不好的”時(shí)，我并不希望暗示批處理的重新編譯一定是不好的：有時(shí)它們避免了一些隱匿的性能問(wèn)題之一，并且它們很少會(huì)影響性能只要不沉迷于SQL代碼，應(yīng)用程序的氣味就不必要了。例如，當(dāng)我們創(chuàng)建要重用的批處理時(shí)，我們總是通過(guò)與參數(shù)sp_ExecuteSQL一起使用來(lái)促進(jìn)代碼重用，或者在應(yīng)用程序中，我們正確地使用綁定參數(shù)。為了謹(jǐn)慎起見，我們使用表變量。

當(dāng)前文章：更改過(guò)程或觸發(fā)器中的SET選項(xiàng)將導(dǎo)致重新編譯-創(chuàng)新互聯(lián)
網(wǎng)頁(yè)路徑：http://bm7419.com/article4/gjsie.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供電子商務(wù)、品牌網(wǎng)站建設(shè)、網(wǎng)站改版、微信小程序、網(wǎng)站制作、全網(wǎng)營(yíng)銷推廣

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)