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

上一篇筆記了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大人多配給資料庫記憶體是正解之一。

 

 

2018.06 博愛特區

 


參考

SQL Server 的 SQL Statistics 物件

CREATE EVENT SESSION (Transact-SQL)

[SQL Server][Statistics]統計值(一)看見統計值

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

sys.trace_events (Transact-SQL)

sys.trace_subclass_values (Transact-SQL)

為了避免在 SQL Server tempdb 資料庫中的配置爭用的建議