學海無邊，書囊無底

「SQL 追蹤」最常用於效能調教與T-SQL 陳述式與預存程序偵錯，此外也提供稽核資料庫活動、收集資料庫樣本資料..等等。

「SQL Server Profiler」是「SQL 追蹤」的圖形化使用者介面可用來監視 Database Engine 或 SQL Server Analysis Services 的執行個體。透過圖型化工具可讓初學者更容易追蹤資料庫活動。

「SQL 追蹤」也提供預存程序可自行定義自己的追蹤，其流程如下：

1.使用 sp_trace_setevent 來指定要擷取的事件。

2.指定事件篩選條件。

3.使用 sp_trace_create 來指定擷取事件資料的目的地。

註：「SQL 追蹤」與 「SQL Server Profiler 」兩者之間，還一項重要的差異：
SQL Server Profiler 在系統負荷過重的狀況下有可能不追蹤某些事件。
但使用「SQL 追蹤」即使在負荷過重的狀況下仍不會略過任何事件。

練習：

以下簡單的示範如何透過SQL Profiler產生「SQL 追蹤」程式碼，追蹤指定的資料庫事件

step1：開啟SQL Profiler工具，連結到要監控的伺服器

step2：設定追蹤屬性

將追蹤擋設定存放到d:\log\mylogtrc.trc檔案，檔案大小上限為5M，並啟用檔案換用

最後設定停只追蹤的時間

勾選顯示所有資料行

勾選所有追蹤events需要記錄欄位，例如DatabaseName、NTUserName、NTDomaiinName、LoginName、HostName、ApplicationName..等

點選資料行篩選按鈕，再跳出的對話窗設定只要追蹤Northwind資料庫事件

點選上圖中的執行按鈕，近入下面追蹤UI畫面

step3：匯出指令碼

從檔案->指令碼追蹤定義檔->對於SQL Server 2005-2008(s)，匯出T-SQL指令碼

step4：修改指令碼

/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler          */
/* Date: 2015/03/11  15:34:48         */
/****************************************************/


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


exec @rc = sp_trace_create @TraceID output, 0, N'D:\log\mytrace', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 7, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 7, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 17, 7, @on
exec sp_trace_setevent @TraceID, 17, 8, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 35, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 11, 7, @on
exec sp_trace_setevent @TraceID, 11, 8, @on
exec sp_trace_setevent @TraceID, 11, 2, @on
exec sp_trace_setevent @TraceID, 11, 6, @on
exec sp_trace_setevent @TraceID, 11, 10, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 11, 11, @on
exec sp_trace_setevent @TraceID, 11, 35, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 12, @on


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

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'Northwind'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

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

error: 
select ErrorCode=@rc

finish: 
go

step5：檢視執行狀態

將指令碼拿到 SQL Server 執行後即可，使用以下指令碼可查詢目前SQL Server 正在執行的追蹤

--在條件式部分，設定不查詢「預設追蹤(Default Trace)」。

SELECT * FROM sys.traces WHERE is_default <> 1

資料行 status 欄位表示目前追蹤狀態(0 = 已停止、1 = 執行中)

step6：檢視追蹤檔內容

 
SELECT TextData,ApplicationName,LoginName,HostName
,StartTime,DatabaseName 
FROM [fn_trace_gettable]('d:\log\mytrace.trc', DEFAULT) 

step7：停止指定的追蹤。

 
EXEC sp_trace_setstatus @traceid = 2 , @status = 0
GO

參數 @status 的說明： 0：停止指定的追蹤。 1：啟動指定的追蹤。 2：關閉指定的追蹤，並從伺服器中刪除其定義。

step8：關閉指定的追蹤，並從伺服器中刪除其定義。

 
EXEC sp_trace_setstatus @traceid = 2 , @status = 2
GO