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

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

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

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

什么是重新編譯？

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

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

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

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

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

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

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

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

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

這些“計劃重用影響”選項，按字母順序排列，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在編譯過程中執(zhí)行“恒定折疊”時，會檢測到這些SET語句，并且似乎在舊版本的SQL Server中，每次調(diào)用該過程時，將其中某些SET選項更改為某些值可能會導(dǎo)致重新編譯。但是，在最新版本的SQL Server中，很少聽到此問題。

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

更改連接設(shè)置

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

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

通過更改SET選項更改結(jié)果

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

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

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

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

得到這個結(jié)果…

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

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

在存儲過程中更改SET選項

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

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

我對這三個過程分別執(zhí)行了兩次，首先是在所有選項均使用“默認(rèn)”設(shè)置的會話中進(jìn)行，其中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í)行設(shè)置與編譯該過程的任何執(zhí)行計劃時有效的執(zhí)行設(shè)置不匹配，則會使用新的set選項創(chuàng)建一個新的緩存計劃。如果我們使用連接相同set_options值重新執(zhí)行相同的存儲過程，則該計劃將被重用。

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

如果您更改了過程中的設(shè)置，則它們僅在該過程中有效，因此它們不會影響調(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í)行的批、準(zhǔn)備好的查詢和動態(tài)SQL。如果發(fā)出“影響計劃重用”SET語句，則對于其中任何一個的緩存計劃都無法如此輕松地重用，并且在SQL Server的早期版本中，每次使用都會有重新編譯的風(fēng)險。

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

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

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

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

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

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

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

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

總結(jié)

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

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

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

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

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

廣告

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