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

點擊下載SQL Prompt正式版

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

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

什么是重新編譯？

當SQL Server執行臨時批處理或查詢或諸如存儲過程或觸發器之類的對象時，SQL Server將為每個批處理或對象以及該批處理或對象中的每個查詢編譯針對當前狀態進行優化的執行計劃數據庫，其對象及其數據。SQL Server的優化器設計此計劃需要花費時間和資源，但是必須在代碼可以傳遞到執行引擎之前完成。幸運的是，我們傾向于重復執行相同的查詢或過程，可能使用不同的參數，因此SQL Server將其生成的大多數計劃存儲在計劃緩存中，并且無論我們使用什么參數值，都將確保所有計劃都可以安全地重用。當我們再次執行相同的批處理或對象時，只要有可能，它將簡單地重用其緩存的計劃。

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

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

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

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

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

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

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

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

當SQL Server在編譯過程中執行“恒定折疊”時，會檢測到這些SET語句，并且似乎在舊版本的SQL Server中，每次調用該過程時，將其中某些SET選項更改為某些值可能會導致重新編譯。但是，在最新版本的SQL Server中，很少聽到此問題。

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

更改連接設置

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

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

通過更改SET選項更改結果

如前所述，會話SET選項的更改在某些情況下可能導致錯誤或警告，或者導致查詢的結果不同。快速演示值得一提，在這里，我將在每批開始時簡單地更改幾個SET選項的值：

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

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

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

得到這個結果…

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

每次更改這些設置選項中的一個時，您都會看到專門為該選項集創建的新計劃，這顯然會增加對緩存的要求以及編譯計劃所花費的CPU時間。如果您對這兩個批次執行十次，您將看到使用了適當的計劃，而無需重新編譯。

本教程內容尚未完結，請點擊下方鏈接查看后續內容，想要查看更多文章資訊請繼續關注我們網站~感興趣的朋友可以下載SQL Prompt試用版免費體驗~

相關內容推薦：

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