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

我們有時在SQL Server監控CPU的效能時,會額外多觀察每秒陳述式重新編譯次數(SQL Re-Compilations/sec)的效能計數,透過收集的資訊及後續的調校來降低生產環境出現大量的重新編譯。

最近專案裡,奉旨把SQL程式也加入持續整合(CI)的測試項目內,在掃描SQL程式時發現幾位成員在頻繁的線上交易語法中,使用了少許的暫存資料表(#TABLE),解釋給同事聽,順便筆記可能引發的大量持續不斷的重新編譯。

這篇先實驗Ad hoc中使用臨時資料表及資料表運算式CTE(ommon table expression),下一篇實驗預存程序(Prepared與預存程序類似)。

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

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

 


準備環境

雖然最近很忙,但為了趕上即將開始的2018 世界盃,我們製作一個會內賽國家的資料表作為範例,測試資料庫的版本是SQL 2014及SQL 2017。

CREATE DATABASE FIFAWorldCup2018
GO

USE FIFAWorldCup2018
CREATE TABLE Teams
(
CountryName nvarchar(30),
FIFARanking int,
GroupID varchar(1)
)
INSERT INTO Teams VALUES('Russia',65,'A'),('Saudi Arabia',63,'A'),('Egypt',30,'A'),('Uruguay',17,'A')

好的,我們同時也有了A組的4支隊伍:地主俄羅斯、沙烏地阿拉伯、埃及及烏拉圭。

 


建立擴充事件追蹤

(1)建立擴充事件來追蹤兩個編譯的事件: 

CREATE EVENT SESSION [TSQLCompilations] ON SERVER 
ADD EVENT sqlserver.query_post_compilation_showplan(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[database_name]=N'FIFAWorldCup2018')),
ADD EVENT sqlserver.sql_statement_recompile(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[database_name]=N'FIFAWorldCup2018')) 
ADD TARGET package0.event_file(SET filename=N'C:\temp\db\TSQLCompilations1.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=NO_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

 

query_post_compilation_showplan

在編譯 SQL 陳述式之後發生。這個事件會傳回編譯查詢時所產生之估計查詢計劃的 XML 表示。使用這個事件可能會對效能帶來重大的額外負擔,所以應該只在短時間進行特定問題的疑難排解或監視時使用。

sql_statement_recompile

在任何種類的批次需要陳述式層級重新編譯時發生。這包括預存程序、觸發程序和隨選批次和查詢。批次可能透過數種介面提交,包括 sp_executesql、動態 SQL、Prepare 方法或 Execute 方法。

 

(2)啟動擴充事件

ALTER EVENT SESSION TSQLCompilations ON SERVER
STATE = START;   -- STOP;

 


測試Ad hoc查詢(暫存資料表)

(1)使用暫存資料表的Ad hoc查詢,總共執行6次

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
GO 6

 

打開擴充事件紀錄,果然出現語法需要重新被編譯的情形

 

編譯原因(Recompile_cause): Temp Table Changed

 

使用Profiler,更即時的發現重新編譯(STMT:Recompile)的事件,由於建立暫存資料表(#TABLE)後,還沒執行過統計值更新,因此重新編譯的事件都是發生在使用暫存資料表的Statement上,而不是建立暫存資料表的Statement。

 


測試Ad hoc查詢(CTE)

使用CTE的Ad hoc查詢,總共執行6次

WITH CTE_DATA AS
(
SELECT * FROM Teams
WHERE FIFARanking= CONVERT(INT,RAND() * 10))

SELECT * FROM CTE_DATA
GO 6

 

Profiler一個重新編譯事件都沒錄到

隔了幾分鐘,點開擴充事件紀錄檔案(還是只有12個事件,表示使用CTE時並沒有出現重新編譯)

 

透過Ad hoc使用臨時資料表(#TABLE)時,若想顯示估計執行計畫(熱鍵Ctrl + L),SQL會出現無效的物件名稱錯誤,當這條Session執行過後,才能顯示預估。

 

*一般資料表運算式(CTE)和子查詢類似,不像臨時資料表(#TABLE)實體存在於Tempdb內。

臨時資料表(#TABLE)

一般資料表運算式(CTE)

 


小結

  • 經由以上的實驗發現:在Ad hoc查詢下,只要使用了暫存資料表(#TABLE),就會導致陳述式需要重新編譯
  • 避免使用暫存資料表(#TABLE)在頻繁的線上交易上。
  • 要使用暫存資料表也應該避免直接組SQL字串(Adhoc),應該要給SQL參數(prepared)。
  • 以Ad Hoc方式使用CTE時,沒有出現重新編譯的情形
  • 如果資料庫版本允許(SQL 2014/2016以後),可以考慮使用記憶體最佳化資料表取代,畢竟Tempdb只有一個。
  • 如果系統中Ah hoc交易太多,也許可以先設定optimize for ad hoc workloads ,減輕過多執行計畫記憶體帶來的壓力。

 

有人說: Messi是阿根廷的Salah

 


參考

SQL Server 的 SQL Statistics 物件

CREATE EVENT SESSION (Transact-SQL)

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

針對特定工作負載最佳化伺服器組態選項