朕在coding

前言

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

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

因為本身負責的業務還有管理資料庫，也會有同事來問說，我的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的功能，達到自動化的效果。

結論

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