[SQL Server]暫存資料表(#TABLE)引發的重新編譯Re-Compilations(SP)

2018-06-09

1953
0
SQLPerformance
2018-06-18

上一篇筆記了Ad hoc使用暫存資料表(#Table)引發的重新編譯(Recompilations)，過量的重新編譯會增加CPU的負擔，換個方式執行SQL，如果預存程序(Stored Procedure)中使用暫存資料表(#TABLE)會不會造成每次執行就重新編譯?

印象中是不一定(抓頭)，來檢定記憶。

*透過.NET程式存取SQL Server資料時:

如果是透過預存程序，Cache Object Type=PROC。
沒有透過預存程序，但有給SQL參數時，Cache Object Type=Prepared。
沒有透過預存程序，只單給SQL Text時(組SQL字串)，則是Cache Object Type=Ah hoc。

測試資料準備

補上2018 FIFA World Cup B組的資料

INSERT INTO Teams VALUES('Portugal',3,'B'),('Spain',8,'B'),('Morocco',48,'B'),('Iran',34,'B')
SELECT * FROM Teams

我們有了A組和B組的國家資料了

建立使用暫存資料表及CTE的預存程序

這邊分別使用暫存資料表(#TABLE)及CTE作為實驗組測試(uspTestTempTableRecompile、uspTestCTERecompile)，同時加了一段使用暫存資料表(#TABLE)，而且有使用Alter語法的預存程序作為對照(uspTestTempTableAlterRecompile)。

--(1)建立使用臨時資料表的預存程序
CREATE PROCEDURE uspTestTempTableRecompile
AS
IF  OBJECT_ID('tempdb..#DATA') IS NOT NULL 
   DROP TABLE #DATA;
SELECT *   INTO #DATA FROM Teams
WHERE FIFARanking = CONVERT(INT,RAND() * 10)
SELECT * FROM #DATA

--(2)建立使用CTE的預存程序
CREATE PROCEDURE uspTestCTERecompile
AS
WITH CTE_DATA AS
(
SELECT * FROM Teams
WHERE FIFARanking = CONVERT(INT,RAND() * 10))
SELECT * FROM CTE_DATA

--(3)建立使用臨時資料表的預存程序(ALTER)
CREATE PROCEDURE uspTestTempTableAlterRecompile
AS
IF  OBJECT_ID('tempdb..#DATA') IS NOT NULL 
   DROP TABLE #DATA;
SELECT *   INTO #DATA FROM Teams
WHERE FIFARanking = CONVERT(INT,RAND() * 10)
ALTER TABLE #DATA ADD C1 VARCHAR(1)
SELECT * FROM #DATA


--先各執行一次
EXEC uspTestTempTableRecompile 
EXEC uspTestCTERecompile
EXEC uspTestTempTableAlterRecompile

確認好環境後，打開監控。*監控的擴充事件與SQL Profiler和上一篇設定相同。

開始實驗

(1)先執行單純使用臨時資料表(#TABLE)的預存程序6次

EXEC uspTestTempTableRecompile 
GO 6

執行結果: 有2次篩選到西班牙!!

(雖然這屆西班牙不被看好，只排第8，但西班牙必勝(綁頭帶)!!!)

這一組的重點：雖然使用了暫存資料表#table，但沒有錄到重新編譯(Recompile)。和Ad hoc不同！SP使用臨時資料表沒有一定要重新編譯。

少了臨時資料表的統計值更新會不會使得SQL Query Optimizer選擇到不好的連結演算法?這個問題先保留下來，我們繼續實驗，最後再探討。

(2)執行使用CTE的預存程序6次

EXEC uspTestCTERecompile
GO 6

如預期的，和ad hoc相同，也沒有發生重新編譯

(3)執行使用臨時資料表且中間有執行Alter #TABLE的預存程序6次

EXEC uspTestTempTableAlterRecompile 
GO 6

查詢結果，確認有C1的資料行(欄位)

發生了6次重新編譯

從擴充事件查看也是6次

從擴充事件明細發現造成重新編譯的原因: Schema changed

預存程序的臨時資料表不需要更新統計值?

回到剛剛實驗預存程序使用臨時資料表(#TABLE)的延伸問題，預存程序使用臨時資料表時，不需要更新統計值更新嗎?

(1)再新增一支預存程序uspTestTempTableRecompileDeferredcompile，並把條件作修改，待會也觀察執行計畫的預估筆數與實際筆數。

CREATE PROCEDURE dbo.uspTestTempTableRecompileDeferredcompile
AS
IF  OBJECT_ID('tempdb..#DATA') IS NOT NULL 
   DROP TABLE #DATA;
SELECT *   INTO #DATA FROM Teams
WHERE FIFARanking >= 0

SELECT * FROM #DATA

(2)打開Profiler，除了編譯事件外，再多補捉SQL陳述式開始(Starting)及結束(Completed)。

(3)執行預存程序5次。

EXEC uspTestTempTableRecompileDeferredcompile 
GO 5

在擴充事件攔截到到1次編譯，造成重新編譯的原因是Deferred compile。

打開包括實際執行計畫，再執行一次uspTestTempTableRecompile ，從執行計畫觀察，實際資料列7筆與估計的6筆也接近，暫存資料表統計值有更新。

從Profiler觀察，在第一次執行這支預存程序，遇到使用暫存資料表時，會先編譯才去執行(延遲編譯：deferred compile)，第二次執行預存程序也不會再發生延遲編譯了。

在預存程序中使用暫存資料表還是會編譯，但可能只有第一次，而且是延遲編譯。

想到一個有趣的再延伸問題，如果某次執行中，臨時資料表的資料量與第一次差距很大，暫存資料表的統計值會不會更新?

如果沒有更新統計值，又剛好這個查詢中，臨時資料表要和其他資料表Join，也許就會導致SQL Query Optimizer該選擇合併或雜湊連結(Merge\Hash Join)卻選擇巢狀迴圈(Nested Join)而導致效能低落。這個我們留到下回揭曉。

所有可能重新編譯的原因

可以透過 catalog views來查詢(sys.trace_events及sys.trace_subclass_values)

SELECT
	tsv.subclass_name
	,tsv.subclass_value
FROM sys.trace_events AS te
JOIN sys.trace_subclass_values AS tsv
	ON te.trace_event_id = tsv.trace_event_id
		AND te.name = 'SP:Recompile'
		AND tsv.subclass_value < 100
ORDER BY tsv.subclass_value;

在SQL2014及SQL2017都是14個分類

小結

使用Ad hoc執行SQL程式時，只要讀取到臨時資料表(#TABLE)都會造成重新編譯。
預存程序或Prepared type中，使用到臨時資料表不一定會重新編譯，但程式中若有對臨時資料表(#TABLE)Alter Table、建立索引或其他觸發統計值更新的條件，都會造成每次重新編譯。
預存程序使用CTE也和Ad hoc使用相同，都不會造成重新編譯(CTE和子查詢是類似的存取啊)。
雖然預存程序或是PrePared type使用臨時資料表不一定觸發重新編譯，但tempdb只有一個，交易也是有可能因為tempdb的資源而被封鎖(dbid:2)。
最近另一組同事分享專案碰到的技術問題，由於資料庫伺服器記憶體不足，執行大型交易的CTE時會有效能問題出現，後來同事改用暫存資料表(#TABLE)。
勾夾SP和DBA大人多配給資料庫記憶體是正解之一。