[MSSQL] 如何建立SQL Trace

  • 2294
  • 0

SQL Profiler 和 SQL Trace的應用

前言

在工作上,常常遇到使用者打電話來說資料庫好慢,問他們說是哪個程式慢,得到的答案卻是通通都很慢,說實在的讓人非常無言也很無助。

但這也不能怪使用者,因為這就是使用者體驗,感覺的這種事情本來就很難用言語來描述。

因為本身負責的業務還有管理資料庫,也會有同事來問說,我的XX table,資料不知道被誰壓掉了,說真的,誰會知道= ='。

如在工作上有以上困擾,SQL Profiler將可幫助脫離苦海。

 

SQL Profiler UI 操作

SQL Server Management Studio工具提供友善的UI介面,只需透過點選的方式便可幫助紀錄SQL的活動。操作步驟如下所示

步驟1:

步驟2

  • 選取New Trace,會出現Sql的登入介面,登入的帳號須具備Alter Trace的權限
  • 填寫Trace name,名稱可自訂
  • 選擇適當的template,可先用Standard(default)
  • 設定存放的路徑

步驟3(Optional),切換到Events頁籤,可自訂監看的欄位和事件

步驟4 : 按下Run,就可開始記錄SQL執行的情形

到這裡,其實已經可以解決上述所提到的一些問題,但畢竟透過UI操作還是需要透過人工的方式,假設有需求是我在固定某時段才要監控SQL行為,或者每個時段監控的事件不相同,則透過UI操作還是略嫌麻煩,如果能把他自動化,應該會更加的方便。

 

Trace自動化

透過工具可以幫助我們產生SQL語法

產生的語法分為下列幾個步驟來解析

步驟1:定義相關的變數,並且執行sp_trace_create預存程序,@TraceID可用來追蹤建立起來的trace,maxfilesize=5則是每個trace檔案限制為5MB,如需更大的容量只需修改這裡的值即可,最後一個Null的值是停止時間,假如有預期的停止時間,也可當作參數設定進來。

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

步驟2:根據步驟1取得的@TraceID,可以透過sp_trace_setevent客製化要監看那些資訊。

  • 第一個參數@TraceID,是步驟1所建立的Trace參考
  • 第二個參數是event_id
  • 第三個參數是column_id
  • 第四個參數是開啟事件=1或關閉事件=0

event_id和column_id的對應表可參考此處

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 12, @on

 搭配UI介面的對應請參考下圖,如果記得起對應表的話,可透過UI介面勾選想要監看的項目後產生SQL語法會比較方便。

步驟3:設定要濾掉的資訊,這個就看個人需求來做設定就可以。

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 28dde5ad-c69c-438b-b7b1-eded752afbe6'

 步驟4:啟動Trace,透過呼叫sp_trace_setstatus,第二個參數是狀態,0=停止,1=啟動,2=關閉並刪除此Trace

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

步驟5:如還有後續動作,可將@TraceID和@rc(0為無錯誤)回傳。 


-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

有了SQL語法就能建立成預存程序,接著設定SQL排程,便可針對想記錄的時間點開啟或關閉Trace的功能,達到自動化的效果。

如有需要查詢目前資料庫有哪些Trace在執行,可透過SELECT * FROM sys.traces
SQL Server Profiler在系統負荷過重時有可能不會追蹤某些事件;但SQL Trace即使在系統負荷過重仍不會省略任何事件。需特別注意

結論

透過SQL Trace的功能,可以幫助我們監看SQL運行的狀況,找出效能的瓶頸,也可稽核資料庫的活動,甚至透過錄製到的SQL語法來Debug線上的系統。