史丹利好熱

這篇先實驗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]統計值(一)看見統計值

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