sqlserver 存儲過程重編譯優缺點、引發原因分析及避免
存儲過程通過明確地將查詢的可變部分轉換爲參數來增進執行計劃的可重用性。這使執行計劃在查詢以可變部分的相同或不同值重新提交時可以被重用。存儲過程包含一組複雜的SQL語句，使生成存儲過程的執行計劃的代價有些高。因此，通常重用存儲過程的執行計劃來代替生成新計劃是有利的。但是，有時候現有的計劃可能不適用或者在重用期間可能不能提供最佳的處理策略。SQL Server重編譯存儲過程中的語句來生成一個新的執行計劃，以解決這個問題。
一、重編譯的好處
存儲過程的重編譯既有好處又有缺點，有時候，爲查詢考慮一個新的處理策略而不是重用現有計劃可能更經濟。特別是表(或對應的統計)中數據的分佈變化或者表中添加了新的索引時。SQL Server 2008中的重編譯在語句級別上。語句級重編譯降低開銷是因爲只重編譯單獨的語句，而不是存儲過程中的所有語句。而在SQL Server 2005以前重編譯方法導致存儲過程一次又一次地被完全重編譯。
創建一個存儲過程並執行，代碼如下：
CREATE PROC dbo.getPerson AS SELECT Id,Name,Age FROM PersonHunderThousand WHERE Name = '夏娩好' EXEC dbo.getPerson
目前執行當前存儲過程的執行計劃如下：
假如我們建了一個覆蓋索引之後：
CREATE NONCLUSTERED INDEX IX_Name ON PersonHunderThousand(Name) INCLUDE(Age)
明顯，再次執行該查詢，使用索引的執行計劃更有利：
SQL Server自動重編譯了存儲過程以有效使用索引。
在這種情況下，花費額外的CPU週期重新編譯存儲過程建立更好的執行計劃是有利的。
SQL Server自動偵測需要重新編譯現有計劃的條件。SQL Server根據特定的規則確定現有計劃需要重新編譯的時機。如果存儲過程的一個特定實現進入重編譯規則的範圍(執行計劃老化、SET選項變化等)，則存儲過程將在每次符合重編譯要求時重新編譯，SQL Server可能不能生成更好的執行計劃。
二、重編譯存儲過程的缺點
在SQL Server Profiler中，SP_Recompile事件用於跟蹤語句重編譯。也可以使用SQL:StmtRecompile事件，這是存儲過程事件。
上面創建的索引是有益於存儲過程裏面的語句的，這次我們先刪除剛剛創建的覆蓋索引，然後換成一個對查詢語句裏無益的索引：
EXEC dbo.getPerson GO CREATE NONCLUSTERED INDEX IX_Name ON PersonHunderThousand(InCome) GO EXEC dbo.getPerson
我們來看SQL Server Profiler監控的輸出：
我們看到，重新編譯對該存儲過程無益。但是不幸地，它進入了導致SQL Server在每次執行時重新編譯存儲過程的條件範圍。這使存儲過程緩衝效率降低，並且將CPU週期浪費在重新生成相同的計劃上。因此，知道導致存儲過程重新編譯的條件，並且在實現存儲過程時用一切辦法避免這些條件是很重要的。當然，這個例子有點牽強，誰沒事會一天到晚建索引啊。
三、確認導致重編譯的語句
SQL Server可以重新編譯存儲過程中的單條語句或整個存儲過程。因而，爲了找到重編譯的起因，確認不能重用現有計劃的SQL語句是很重要的。
可以使用Profiler工具來跟蹤存儲過程重編譯。也可以使用Profiler工具來確認導致存儲過程重編譯的語句。
分析存儲過程重編譯的事件和數據列：
| 事件 | 數據列 |
| SP:Completed | EventClass |
| SP:Recompile | TextData |
| SP:Starting | EventSubClass |
| SP:StmtCompleted(可選) | SPID |
| SP:StmtStaring(可選) | StartTime |
考慮下面簡單的存儲過程:
CREATE PROC dbo.InsertPerson AS CREATE TABLE t1(c1 INT) INSERT INTO t1( c1 )VALUES(42); --數據修改導致重編譯
輸出如下：
從輸出可以看到，有一個重編譯事件(SP:Recompile)，表示該存儲過程經受了重編譯。當存儲過程第一次執行時，SQL Server編譯存儲過程並生成一個執行計劃。
執行計劃值維持在易失性的內存中，它們在SQL Server重啓時被拋棄。在服務器重啓後的存儲過程下一次執行時，SQL Server再次編譯存儲過程並且生成執行計劃。這些編譯不被看做存儲過程的重編譯，因爲該計劃不存在於緩衝中用於重用，SP:Recompile事件表示計劃已經存在但是不能被重用。爲了瞭解那條語句引起的重編譯，需要查看SP:Recompile事件中的TextData列，它明確地說明被重編譯的語句。還可以使用SP:StmtStarting事件和重編譯事件的組合來確認導致重編譯的存儲過程語句。SP:Recompile事件緊接在SP:StmtStarting事件之後發生表明該存儲過程語句導致重編譯。使用TextData列更簡單，但是在非常複雜的過程中，使用SP:StmtStarting事件可能更有意義。
注意，在存儲過程重編譯之後，導致重編譯的存儲過程語句再次啓動以執行新的計劃。可以使用SP:StmtStarting事件或SP:StmtCompleted事件以確認導致重編譯的存儲過程語句。
------------------------------------------------------------------------------------
爲了改進性能，分析重編譯的起因很重要。往往，重編譯可能並不需要，可以避免它以改進性能。瞭解導致重編譯發生的不同條件有助於評估重編譯的起因，並決定在重編譯不必要時避免它的方法。
存儲過程重編譯在以下情況下發生：
- 存儲過程語句中引用的常規表、臨時表或視圖的架構變化。架構變化包括表的元數據或表上索引的變化；
- 常規或臨時表的列上的綁定(如默認/規則)變化。
- 表索引或列上的統計的變化超過一定的閾值。
- 存儲過程編譯時一個對象不存在，但是在執行期間創建(延遲對象解析)；
- SET選項變化；
- 執行計劃老化並釋放；
- 對sp_recompile系統存儲過程的顯式調用。
- 顯式使用RECOMPILE子句；
可以在Profiler中看到這些變化。原因由SP:Recompile事件的EventSubClass數據列值指出:
| EventSubClass | 描述 |
| 1 | 常規表或試圖的結構或綁定變化 |
| 2 | 統計變化 |
| 3 | 在執行期間創建存儲過程計劃中不存在的對象 |
| 4 | SET選項變化 |
| 5 | 臨時表架構或綁定變化 |
| 6 | 遠程行集的架構或綁定變化 |
| 7 | FOR BROWSE許可變化 |
| 8 | 查詢通知環境變化 |
| 9 | MPI視圖變化 |
| 10 | 遊標選項變化 |
| 11 | 調用WITH RECOMPILE選項 |
一、架構或綁定變化
當一個視圖、常規表或臨時表的架構或綁定變化時，現有的存儲過程執行計劃將作廢。執行任何引用這些對象的語句之前該存儲過程必須重編譯。SQL Server自動偵測這種情況並且重編譯存儲過程。
因此，要避免架構變化，儘量不要在存儲過程中涉及到臨時表，索引，字段的操作，即不要交替使用DML、DDL語句。
在存儲過程中，DDL語句通常用於創建局部臨時表以及修改他們的架構(包括添加索引)。這樣做可能影響現有計劃的有效性，並且可能在引用該表的存儲過程語句被執行時導致重編譯。
如：
CREATE PROC sp_Test AS CREATE TABLE #TB1(ID INT,DSC NVARCHAR(50)) INSERT INTO #TB1(ID,DSC) SELECT TOP 100 ID,NAME FROM PersonTenThousand; --第1次重編譯 SELECT * FROM #TB1 AS TT --第2次重編譯 CREATE CLUSTERED INDEX IXID ON #TB1(ID); SELECT TOP 10 * FROM #TB1; --第3次重編譯 CREATE TABLE #TB2(c1 INT); SELECT * FROM #TB2; --第4次重編譯 GO EXEC sp_Test GO EXEC sp_Test
SQL Server Profiler跟蹤輸出：
可以看到存儲過程被重編譯4次。
- 第一次重編譯來自於多了個表#TB1，現有自行計劃不包含表#TB1的任何信息。
- 第二次重編譯來自於臨時表數據的變化；
- 第三次重編譯來自於臨時表架構變化，多了個索引；
- 第四次衝編譯多了表#TB2，因爲是新建的表，現有執行計劃沒有關於#TB2的任何信息；
二、統計變化
SQL Server記錄表的變化數量。如果變化數量超過重編譯閾值(RT),SQL Server自動在存儲過程中引用該表時更新統計。當偵測到自動更新統計的條件時,SQL Server自動重新編譯存儲過程並更新統計。
RT由一組取決於表是永久表或臨時表(不是表變量)以及表中的行數的公式來確定。表10-3顯示了基本的公式，可以確定由於數據變化引起的語句重編譯預期時間。
| 表類型 | 公式 |
| 永久表 | 如果n(行數)<=500,則RT=500; 如果n>500，則RT=500+.2*n |
| 臨時表 | 如果n<6，則RT=6; 如果6<=n<=500，則RT=500; 如果n>500，則RT=500+.2*n |
統計變化引起的重編譯可能生成一個和前一個計劃相同的計劃，在這種情況下，重編譯是沒有必要的，如果開銷較大則應避免。
避免統計的變化而引起的重編譯有兩個方法：
- 使用KEEPFIXED PLAN選項；
- 禁用該表上的自動更新統計特性；
1、使用KEEPFIXED PLAN選項
SQL Server提供KEEPFIXED PLAN選項來避免因爲統計變化引起的重編譯。
CREATE PROC GetPerson AS SELECT * FROM Person WHERE Id = 1 OPTION(KEEPFIXED PLAN);
如，像上面這種寫法，執行存儲過程就不會再因爲表Person上的統計變化而引起重編譯。
2、禁用表上的自動更新統計
也可以通過禁用相關表上的自動更新統計來避免統計更新引起的重編譯。(不過這種方法就好比，腳疼就把整個腳砍掉，不可取)。
例如，可以關閉表Person上的自動更新統計
EXEC sp_autostats 'Person','OFF'
雖然這種方式可以避免重編譯，但是使用這種技術應該非常小心，因爲過時的統計可能對基於開銷的優化器有負面的影響，如果禁用統計的自動更新，應該有一個定期更新統計的SQL任務。
三、延遲對象解析
存儲過程通常動態創建然後訪問數據庫對象。當這樣的存儲過程第一次執行時，第一個執行計劃不包含關於運行時創建的對象的信息。因此，在第一個執行計劃中，對這些對象的處理策略被延遲到存儲過程的運行時。
當執行一個引用這些對象的DML語句時，存儲過程被重新編譯以生成一個包含該對象處理策略的新計劃。
在存儲過程中可以創建常規表和局部臨時表來保存中間結果。由於延遲對象解析引起的存儲過程重編譯對於常規表和局部臨時表來說有所不同。
1、由於常規表引起的重編譯
爲了理解在存儲過程中創建常規表所致的存儲過程重編譯問題，考慮以下實例:
CREATE PROC dbo.p1 AS CREATE TABLE dbo.p1_t1(c1 INT); --存儲過程開始時表不存在 SELECT * FROM dbo.p1_t1; --導致重編譯 DROP TABLE dbo.p1_t1 --結束後刪除該表 GO EXEC dbo.p1 --第一次執行 EXEC dbo.p1 --第二次執行
SQL Server Profiler輸出：
第一次執行該存儲過程時，執行計劃在存儲過程實際執行之前生成。
但是如果在存儲過程創建之前存儲過程中創建的表不存在，引用該表的SELECT語句尚不會有執行計劃。因此爲了執行SELECT語句，存儲過程必須重編譯。可以看到，在第二次執行時SELECT語句被重編譯，在第一次執行期間卸載存儲過程中的表並沒有卸載保存在過程緩衝中的存儲過程計劃。SQL Server考慮爲其表架構的一次變化，因此SQL Server在存儲過程後執行SELECT語句之前重新編譯存儲過程。因此，看到第一次的重編譯依然發生在BatchStarting之後。
2、由於局部臨時表引起的重編譯
大部分時候，在存儲過程中創建局部臨時表而不是常規表。爲了禮節局部臨時表對存儲過程重編譯的不同影響，修改前面的實例，只用一個臨時表替換常規表。
CREATE TABLE dbo.p1 AS CREATE TAVLE #p1_t1(c1 INT) --指定本地臨時表 SELECT * FROM #p1_t1 --在第一次執行時導致重編譯 DROP TABLE #p1_t1 GO EXEC dbo.p1 --第一次執行 EXEC dbo.p1 --第二次執行
因爲局部臨時表在存儲過程執行結束時自動卸載，所有沒有必要明確地卸載臨時表。但是，在局部臨時表工作完成之後馬上卸載它是一個好的編程習慣。
最好分開點擊，這樣SQL Server Profiler生成的監控比較清晰：
從監控輸出可以看到，第一次執行時存儲過程被重編譯。對應的EventSubClass值支出的重編譯起因和常規表上的一樣，但是，存儲過程在重新執行時不被重編譯，這和常規表不同。
存儲過程後續執行期間的局部臨時表架構與前一次執行時保持一致。局部臨時表不可用於存儲過程的範圍之外，所以其架構無論執行多少次都是一致的，因此，SQL Server在存儲過程後續執行期間確定能安全地重用現有計劃，避免了重編譯。
臨時表會引起存儲過程重編譯，想要避免可以使用表變量代替。
四、SET選項變化
在存儲過程的執行計劃取決於環境設置。如果環境設置在存儲過程中變化，則SQL Server在每次執行時重編譯存儲過程。
CREATE PROC dbo.p1 AS SELECT 'a' + null + 'b'; --第一次拼接 SET CONCAT_NULL_YIELDS_NULL OFF; SELECT 'a' + null + 'b'; --第二次拼接 SET ANSI_NULLS OFF; SELECT 'a' + null + 'b'; --第三次拼接 GO EXEC dbo.p1 --第一次執行 EXEC dbo.p1 --第二次執行
SQL Server Profiler輸出如下：
因爲SET NOCOUNT沒有修改環境設置，不像前面看到的用於修改ANSI設置的SET語句，SET NOCOUNT不會導致存儲過程重編譯。
在存儲過程中修改SET選項導致SQL Server在執行SET語句後面的語句之前重編譯該存儲過程。因此，這個存儲過程分別在SET語句後被重編譯兩次。
但如果在這之後，在執行存儲過程：
EXEC dbo.p1 --第三次執行 EXEC dbo.p1 --第四次執行 EXEC dbo.p1 --第五次執行
都不會再重編譯，因爲那些內容現在已經成爲了執行計劃的一部分。
因此，如果想要避免由於SET選項變化引起的存儲過程重編譯時，儘量不要在存儲過程中設置SET選項。
另外，SET NOCOUNT選項是一個例外，它沒有修改環境設置，不像前面示例中的那樣會導致重編譯。
五、執行計劃老化
SQL Server通過維護緩衝中執行計劃的壽命來管理過程緩衝的大小，如果一個存儲過程長時間未被重新執行，執行計劃的壽命字段將下降爲0，內存短缺時將把該計劃從緩衝中刪除。當這種情況發生並且存儲過程被重新執行時，將生成一個新計劃並將其緩衝到過程緩衝中。但是，如果系統中有足夠的內存，未使用的計劃在內存壓力增加之前不會被刪除。
六、顯式調用sp_recompile系統存儲過程
SQL Server還提供了sp_recompile系統存儲過程來手工標記需要重編譯的存儲過程。這個系統存儲過程可以在表、視圖、存儲過程或觸發器上調用。如果在存儲過程或觸發器上調用，則該存儲過程或觸發器在下次執行時被重編譯。在表或視圖上調用標記所有調用該表/視圖的存儲過程和觸發器在下次執行時重新編譯。
如在表Person上調用sp_recompile，則所有調用Person表的存儲過程和觸發器被標記爲需要重編譯，在下次執行時重新編譯：
sp_recompile 'Person'
也可以使用sp_compile來使用sp_executesql執行時指定撤銷重用現有計劃。注意在 SQL Server Profiler集合中，指定重編譯存儲過程的記錄事件 SP:CacheInsert 而不是事件 SP:Recompile。指定表纔是SP:Recompile。
示例：
CREATE PROC GetPerson @Id int AS SELECT * FROM PersonTenThousand WHERE ID = @Id GO EXEC GetPerson @Id = 9876 --連續執行3次，都沒有重編譯 sp_recompile 'GetPerson' --指定重編譯存儲過程 EXEC GetPerson @Id = 9876 --這次重編譯了 sp_recompile 'PersonTenThousand' --指定對引用表的存儲過程重編譯 EXEC GetPerson @Id = 9876 --這次也重編譯了
捕獲如下：
七、顯示使用WITH RECOMPILE子句
SQL Server允許使用CREATE PROCRDURE或EXECUTE的RECOMPILE子句顯式地重編譯一個存儲過程。這些方法降低了計劃可重用性的效率，所以只應該在一些特殊的場合使用它們。
1、CREATE PROCEDURE語句的RECOMPILE子句
有時候，存儲過程的計劃需求可能隨着調用存儲過程的參數值變化而變化。在這種情況下，重用使用不同參數值的該計劃可能降低存儲過程的性能，可以使用CREATE PROCEDURE語句的RECOMPILE子句來強制每次執行存儲過程都生成一個新計劃(僅僅新生成執行計劃，並非重編譯整個存儲過程)。
示例：
CREATE PROC GetPerson @Id int WITH RECOMPILE AS SELECT * FROM PersonTenThousand WHERE ID = @Id GO EXEC GetPerson @Id = 9876 --每次都重新生成執行計劃
SQL Server Profiler監控輸出如下：
2、Execute語句的RECOMPILE子句
存儲過程中的特定參數值可能需要不同的執行計劃，可以在執行存儲過程時動態採用WITH RECOMPILE，對特定一次執行重新生成新計劃。
CREATE PROC GetPerson @Id int AS SELECT * FROM PersonTenThousand WHERE ID = @Id GO EXEC GetPerson @Id = 9876 --不重新生成計劃 EXEC GetPerson @Id = 9876 WITH RECOMPILE --新生成計劃
SQL Server Profiler輸出：
當存儲過程使用WITH RECOMPILE子句執行時，將臨時生成一個新計劃。這個新的計劃不會被緩衝，並且不會影響現有計劃。當存儲過程不使用RECOMPILE子句執行時，該計劃和往常一樣被緩衝。僅僅影響一次執行，這與CREATE PROCDURE不同。因爲可以考慮創建不同的存儲過程來代替使用CREATE PROCDURE時RECOMPILE。
八、使用OPTIMIZE FOR查詢提示
儘管不總是能減少或消除重編譯，但是使用OPTIMIZE FOR查詢提示可以幫助你使用指定的參數值來編譯計劃，而不管調用的應用程序傳入的參數值。
如：
CREATE PROC dbo.getPerson @Id INT AS SELECT * FROM Person INNER JOIN Province WHERE Person.Id = @Id OPTION (OPTIMIZE FOR(@Id = 1)) --指定使用Id爲1的參數來生成執行計劃
執行：
EXEC dbo.getPerson @Id = 1234 WITH RECOMPILE --強制重編譯 EXEC dbo.getPerson @Id = 5678 WITH RECOMPILE --強制重編譯
現在，執行存儲過程都重編譯了，但是每次都是根據Id爲1來生成執行計劃。不會因爲傳入的參數不同而改變。
參考