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

---

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

點擊下載SQL Prompt正式版

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

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

什么是重新編譯？

當(dāng)SQL Server執(zhí)行臨時批處理或查詢或諸如存儲過程或觸發(fā)器之類的對象時，SQL Server將為每個批處理或?qū)ο笠约霸撆幚砘驅(qū)ο笾械拿總€查詢編譯針對當(dāng)前狀態(tài)進行優(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ā)生，這時它將標記要重新編譯訪問該表的查詢的所有緩存計劃。下次運行其中一個查詢時，優(yōu)化器將生成新計劃，而舊計劃將被刪除。

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

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

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

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

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

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

這意味著您可以看到多個緩存的計劃，除了這些SET選項的細節(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ū)ο螅哂型耆嗤脑O(shè)置時，才可以重新使用該計劃。雖然以這種方式重新編譯計劃很少會引起主要的性能問題，但確實會帶來CPU成本，并且可能會引起問題，尤其是對于編譯成本高且執(zhí)行頻率高的復(fù)雜查詢，甚至可能同時出現(xiàn)這兩種情況在多語句程序中。

更改連接設(shè)置

對于ODBC、ADO或JDBC連接，為連接的默認設(shè)置指定任何更改的方法是，在首次建立連接后執(zhí)行初步的SET語句批處理。連接字符串中沒有允許該操作的選項：必須由SET語句完成。在SSMS中，您可以使用“查詢”菜單（“查詢” >“查詢選項”）為連接的執(zhí)行行為指定高級和ANSI標準選項。在進行開發(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ù)庫中完成了此操作，因此您需要進行更改）。

得到這個結(jié)果…

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

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

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

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

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

---

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