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

---

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

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

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

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

什么是重新編譯？

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

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

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

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

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

再次，有幾種可能的原因，我們將不在這里進(jìn)一步討論，例如，對(duì)未在過程中靜態(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在編譯過程中執(zhí)行“恒定折疊”時(shí)，會(huì)檢測到這些SET語句，并且似乎在舊版本的SQL Server中，每次調(diào)用該過程時(shí)，將其中某些SET選項(xiàng)更改為某些值可能會(huì)導(dǎo)致重新編譯。但是，在最新版本的SQL Server中，很少聽到此問題。

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

更改連接設(shè)置

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

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

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

如前所述，會(huì)話SET選項(xiàng)的更改在某些情況下可能導(dǎo)致錯(cuò)誤或警告，或者導(dǎo)致查詢的結(jié)果不同?？焖傺菔局档靡惶?，在這里，我將在每批開始時(shí)簡單地更改幾個(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ù)庫中完成了此操作，因此您需要進(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ì)劃，而無需重新編譯。

本教程內(nèi)容尚未完結(jié)，請(qǐng)點(diǎn)擊下方鏈接查看后續(xù)內(nèi)容，想要查看更多文章資訊請(qǐng)繼續(xù)關(guān)注我們網(wǎng)站~感興趣的朋友可以下載SQL Prompt試用版免費(fèi)體驗(yàn)~

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

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

---

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請(qǐng)點(diǎn)擊