上一篇筆記了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)