[SQL SERVER]你不知道的Trace Flag

Trace Flag並非暗黑技巧,它可以讓DBA有更多自主控制權,

有時在處理效能問題時,我不得不承認這是最後手段,

但要清楚明白行為特行,才不會造成無法挽救的悲劇。

Trace Flag是做什麼?

Trace Flag是暫時關閉或設定SQL SERVER特定行為。例如,啟動SQL SERVER並設定3226,

就會關閉將每項成功的備份作業寫入SQL Server錯誤記錄檔和系統事件紀錄,

如果備份頻率高(交易紀錄檔),那麼這些成功訊息可能快速累積,

進而產生龐大的錯誤記錄檔,一來難以搜尋,二來也多少影響SQL Server讀取該檔案效能。

 

如何控制Trace Flag

啟用: DBCC TRACEON (Transact-SQL)

關閉: DBCC TRACEOFF (Transact-SQL)

啟動組態:使用T參數

查詢: QUERYTRACEON trace_flag_number

 

如何確認Trace Flag狀態

顯示目前所有啟用的全域(globally) Trace Flag

DBCC TRACESTATUS(-1);

GO

 

顯示指定Trace Flag狀態

DBCC TRACESTATUS (1222, 3226,2371,1224,1118);

GO

 

顯示指定全域(globally)Trace Flag 狀態

DBCC TRACESTATUS (3226, -1);

GO

 

顯示目前Session所有啟用的Trace Flag

DBCC TRACESTATUS ();

GO

 

顯示可用規則清單

USE master;

GO

DBCC TRACEON(3604);

GO

DBCC SHOWONRULES;

GO

--DBCC SHOWOFFRULES; 關閉可用規則清單

規則名稱描述如下

JN: Join

NL: Nested Loop

LOJN: Left Outer Join

LSJN: Left Semi Join

LASJN: Left Anti Semi Join

SM: Sort Merge

FOJN: Full Outer Join

LOJN: Left Outer Join

ROJN: Right Outer Join

HS: Hash

 

為什麼要定義規則

我們都知道,SQL SERVER執行TSQL會產生執行計畫,由於TSQL並非程序性語言,

像join這類邏輯操作都會對應一物理操作讓SQL SERVER實作,這部分的轉換是基於某些規則的(就像寫程式一樣,A對應AJ..等),

例如我們簡單寫一inner join,QO會依據現有索引和統計值,選擇要使用Hash、Nested Loop或Merge Join來處理,

如果沒有這些定義好的規則,SQL SERVER則無法正確實作任何邏輯操作。

 

真實世界應用

從以前到現在,我透過Trace Flag解決不少問題,但多數人卻不敢使用Trace Flag,

主要原因是BOL沒有詳細介紹每個Trace Flag,這點確實讓我相當困擾,

但前幾天我查看BOL,發現已經更新許多Trace Flag介紹,

這表示DBA對SQL SERVER將有更大自主控制權,

但我要強調,所有Trace Flag請先進行測試,確認沒有任何副作用或其他問題後,

在套用到正式環境,一切請謹慎並清楚明白相關Trace Flag行為和特性才不會造成無法挽救的悲劇。

我舉幾個簡單例子如下。

 

@強制執行平行計畫

option(querytraceon 8649)

@顯示QO特定階段成本估計、時間、任務..等其他資訊

option(querytraceon 8675)

@顯示QO優化過程所使用記憶體資訊

option(querytraceon 2372)

@顯示QO優化過程所使用記憶體資訊和屬性

option(querytraceon 2373)

@強制QO為Full最佳化層級

option(querytraceon 8757)

 

參考

Trace Flags (Transact-SQL)

DBCC TRACEON (Transact-SQL)

DBCC TRACEOFF (Transact-SQL)

QUERYTRACEON trace_flag_number

[SQL SERVER]讓一般使用者執行DBCC TraceON

[SQL SERVER][Memo]Trivial Plan

Some useful SQL Server 2014 trace flags