我們有時在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)