SQL SERVER 臨時表導致儲存過程重編譯(recompile)的一些探討

    SQLSERVER為了確保返回正確的值，或者處於效能上的顧慮，有意不重用快取在記憶體裡的執行計劃，而重新編譯執行計劃的這種行為，被稱為重編譯（recompile）。那麼引發儲存過程重編譯的條件有哪一些呢？下面羅列了一些導致重編譯（recompile）的條件：

    - 對查詢所引用的表或檢視進行更改（ALTER TABLE 和 ALTER VIEW）。

    - 對執行計劃所使用的任何索引進行更改。

    - 對執行計劃所使用的統計資訊進行更新，這些更新可能是從語句（如 UPDATE STATISTICS）中顯式生成，也可能是自動生成的。

    - 刪除執行計劃所使用的索引。

    - 顯式呼叫 sp_recompile。

    - 對鍵的大量更改（其他使用者對由查詢引用的表使用 INSERT 或 DELETE 語句所產生的修改）。

    - 對於帶觸發器的表，插入的或刪除的表內的行數顯著增長。

    - 使用 WITH RECOMPILE 選項執行儲存過程。

    - 有些DBCC FREEPROCCACHE;分離、附加資料庫、資料升級也會清除記憶體裡快取的執行計劃

好了，切入到今天我們要關注的問題：臨時表的資料變化導致儲存過程重編譯問題，其實臨時表的資料變化導致儲存過程重編譯實質上是因為臨時表的資料變化，導致了臨時表統計資訊的自動更新，從而引起的重編譯。那麼觸發臨時表的統計資訊的更新的條件或閥值是什麼呢？說來也簡單，就是下面一個這個公式（n表示變更前臨時表的資料記錄數，確切的說是上一次採集統計資訊時臨時表的記錄數）

Temporary table

1. If n < 6, RT = 6.
2. If 6 <= n <= 500, RT = 500.
3. If n > 500, RT = 500 + 0.20 * n.

有個網友說儲存過程中的臨時表資料變更的閥值有問題：他的原話如下

If n < 6, Recompilation threshold = 6.
If 6 <= n <= 500, Recompilation threshold = 500.

上面這兩個區間沒有問題。但是大於500的之後，根本就不是變化大於20%之後再重編譯。看了他提出的問題，其實我也不是特肯定，畢竟沒有實際驗證過。實踐才是檢驗整理的唯一標準，那麼我們就開始做實驗吧，首先準備一下測試環境（Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) ）.指令碼如下所示：

USE MyDBA;

GO

IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE id=object_id(N'[dbo].[TEST]') AND OBJECTPROPERTY(id, N'IsTable')=1 )

BEGIN

 DROP TABLE dbo.TEST;

    CREATE TABLE TEST

    (

          ID INT IDENTITY(1, 1) ,

          NAME VARCHAR(40)     ,

          CONSTRAINT PK_TEST PRIMARY KEY(ID)

    )

END

GO

INSERT INTO TEST VALUES(NEWID())

GO 10000

CREATE PROCEDURE Usp_Recompile_TEST(@Index INT)

AS

BEGIN

 CREATE TABLE #T(ID   INT , NAME VARCHAR(40));

 INSERT INTO #T SELECT ID, NAME FROM TEST WHERE ID <=@Index;

 SELECT  m.* FROM #T m INNER JOIN TEST n ON m.ID = n.ID

END

GO

準備好測試環境後，那麼此時我們開啟SQL Server工具SQL Server Profiler，選擇“SP：Recompile”和“SP：Complete”事件，然後取消一些選擇列，僅僅選擇一些需要的列，例如 EventClass、TextData等。如下所示

開啟Profile跟蹤後，我們開啟一個會話視窗，勾選“包括實際的執行計劃”，然後再視窗執行下面SQL語句

EXEC dbo.Usp_Recompile_TEST 1;

如下所示，實際的執行計劃中，我們看到“估計行數”和“實際行數”是一致的。

EXEC dbo.Usp_Recompile_TEST 2;

EXEC dbo.Usp_Recompile_TEST 6;

執行上面兩個語句，我們會發現“估計行數”與“實際行數”開始出現偏差，因為資料庫對臨時表#T沒有最新的統計資訊，還是上一次收集的統計資訊時的資料（1行資料）

EXEC dbo.Usp_Recompile_TEST 7;  此時已經觸發了對臨時表統計資訊的採集更新(請見後面闡述)。

EXEC dbo.Usp_Recompile_TEST 130;

EXEC dbo.Usp_Recompile_TEST 500;

EXEC dbo.Usp_Recompile_TEST 506;

EXEC dbo.Usp_Recompile_TEST 507;

那麼執行上面SQL語句，130我們確信不會導致臨時表#T去更新統計資訊，501會觸發#T表的統計資訊更新嗎？ 如果不會觸發，那麼確切的值是多少呢？答案是507，如下截圖所示：

想必有些人會說，我實驗的結果不一樣哦（啪啦啪啦說一大堆），那麼你是否真正的理解了下面公式呢？ n表示臨時表變跟前的記錄數（確切的說是統計資訊採集時的記錄數），後面的RT表示變跟的記錄數。

Temporary table

1. If n < 6, RT = 6.
2. If 6 <= n <= 500, RT = 500.
3. If n > 500, RT = 500 + 0.20 * n.

由於我第一次執行的是EXEC dbo.Usp_Recompile_TEST 1，那麼資料庫的記錄數為1，那麼1+ 6 =7; 也就是上圖EXEC dbo.Usp_Recompile_TEST 7時才觸發臨時表#T的統計資訊更新，而為什麼是507（7+500=507）呢，因為最後一次統計資訊的採集，臨時表#T的記錄數為7 ，所以7+500=507，是否有點不解，那麼你按我這個SQL執行一遍，然後用Profile跟蹤、你會看到下面結果，如果還不太明白，結合截圖好好理解一下：

DBCC FREEPROCCACHE;

EXEC dbo.Usp_Recompile_TEST 2;

EXEC dbo.Usp_Recompile_TEST 6;

EXEC dbo.Usp_Recompile_TEST 7;

EXEC dbo.Usp_Recompile_TEST 8;

如果還沒有理解的話，我的表達能力已到極限了，自己再好好琢磨一下吧！ 那麼接下來才是我們重點想要驗證、測試的。

DBCC FREEPROCCACHE; 

EXEC dbo.Usp_Recompile_TEST 501; 

此時臨時表#T的記錄數為501，那麼當臨時表#T裡面的記錄數變更了多少時，才會觸發統計資訊的更新呢？ 由於是插入，那麼根據公式應該是501 + (500 + 0.2*501) = 1101.2 ，那麼應該是1101，即使是1100也不會變化。下面SQL Server Profile可以驗證我們的推測

EXEC dbo.Usp_Recompile_TEST 1100; 

EXEC dbo.Usp_Recompile_TEST 1101; 

如果我們繼續使用該儲存過程，那麼當引數為什麼值時才會觸發統計資訊更新呢？ 1101 +(500+0.2*1101)=1821.2,也就是說必須是1821才會觸發統計資訊更新，下面SQL Server Profile的截圖也驗證了我們的推測。

EXEC dbo.Usp_Recompile_TEST 1300; 

EXEC dbo.Usp_Recompile_TEST 1320; 

EXEC dbo.Usp_Recompile_TEST 1321; 

EXEC dbo.Usp_Recompile_TEST 1820; 

EXEC dbo.Usp_Recompile_TEST 1821;

所以綜上述實驗驗證，SQL SERVER 臨時表導致儲存過程重編譯(recompile)的那些閥值確實是正確的，也是沒有問題的。當然如有疏漏或不對的地方，敬請指出。