使用 SQL Profiler 建立「SQL 追蹤(SQL Trace)」

「SQL 追蹤(SQL Trace)」
SQL Server 所提供的 Transact-SQL 系統預存程序，可建立 SQL Server Database Engine之執行個體的追蹤。
您可以從自己的應用程式中使用這些系統預存程序以手動建立追蹤，而不是使用 SQL Server Profiler 建立追蹤。 如此一來，就可以依照您的企業需求撰寫自訂的應用程式。
可記錄指定的事件，藉以協助您進行效能的疑難排解、稽核資料庫活動、收集測試環境的樣本資料、為 Transact-SQL 陳述式與預存程序偵錯，以及收集效能分析工具的資料等。

SQL Server Profiler
是「SQL 追蹤」的圖形化使用者介面，可用來監視 Database Engine 或 SQL Server Analysis Services 的執行個體。
您可以擷取每一個事件的相關資料，並將資料儲存至檔案或資料表，以供稍後分析。例如，您可以監視生產環境，查看哪些預存程序由於執行速度過慢而影響效能。

---

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

---

若要使用預存程序來定義自己的追蹤，其流程如下：
1.使用 sp_trace_setevent 來指定要擷取的事件。
2.指定事件篩選條件。
3.使用 sp_trace_create 來指定擷取事件資料的目的地。

但你可以使用 SQL Profiler 建立「SQL 追蹤」。

---

實作練習：使用 SQL Profiler 建立「SQL 追蹤」

工作1：使用 SQL Profiler 產生建立「SQL 追蹤」的程式碼
工作2：檢視與修改 SQL Profiler 所產生的程式碼
工作3：執行與觀察「SQL 追蹤」

工作1：使用 SQL Profiler 產生建立「SQL 追蹤」的程式碼

步驟1：執行 SQL Profiler，連線指定的伺服器。
步驟2：在「追蹤屬性」視窗，設定以下的參數，並參考下圖所示：
勾選「儲存至檔案」，設定此檔案存放到磁碟C。
勾選「啟用檔案換用」。
在「設定檔案大小上限(MB)」，輸入 5，表示每個「SQL 追蹤」的檔案大小為 5 MB。
勾選「伺服器處理追蹤資料」。
點選「執行」。





步驟3：在上方的工具選單，點選「檔案」\「停止追蹤」。
步驟4：在上方的工具選單，點選「檔案」\「匯出」＼「指令碼追蹤定義」，選擇「對於 SQL Server 2005-2008」。

步驟5：在「另存新檔」視窗，設定與輸入此指令碼檔案的檔名與路徑，點選「存檔」\「確定」。
步驟6：關閉 SQL Profiler。


工作2：檢視與修改 SQL Profiler 所產生的程式碼

步驟1：使用 SSMS 管理工具，開啟先前所產生的程式碼。
步驟2：找到以下的程式碼：

-- 原本由 SQL Profiler 所產生的程式碼
exec @rc = sp_trace_create @TraceID output, 2, N'C:\mySQLTrace\ST.trc', @maxfilesize, NULL

/*
若是加入參數名稱，應該會是以下的格式：
exec @rc=sp_trace_create
@traceid=@traceid output, @options=2,
@tracefile=N'C:\mySQLTrace\ST',
@maxfilesize=@maxfilesize,
@stoptime=NULL
*/

有關於參數 @options 與 @tracefile 的說明如下：



在此次範例中，設定值為：
@options = 2。表示使用「TRACE_FILE_ROLLOVER」，建立具備換用的追蹤檔。
@tracefile = N'C:\mySQLTrace\ST'。刪除了 *.trc 附檔名，這是因為 SQL Server 會在所有追蹤檔名稱中，附加 .trc 副檔名。

請自行依據需求，修改為適合的參數值。


工作3：執行與觀察「SQL 追蹤」

步驟1：執行先前在工作2 由 SQL Profiler 所產生的程式碼。
步驟2：執行以下的範例程式碼來查詢系統目前正在執行的追蹤：

--01 查詢系統目前正在執行的追蹤
/*
資料行 status 的說明：
追蹤狀態：
0 = 已停止
1 = 執行中
在條件式部分，設定不查詢「預設追蹤(Default Trace)」。
*/
SELECT * FROM sys.traces
WHERE is_default <> 1

步驟3：若要停止此「SQL 追蹤」，請執行以下的範例程式碼：

--02 停止指定的追蹤。
/*
參數 @status 的說明：
0：停止指定的追蹤。
1：啟動指定的追蹤。
2：關閉指定的追蹤，並從伺服器中刪除其定義。
*/
EXEC sp_trace_setstatus @traceid = 2 , @status = 0
GO
--03 關閉指定的追蹤，並從伺服器中刪除其定義。
EXEC sp_trace_setstatus @traceid = 2 , @status = 2
GO
--04 查詢系統目前正在執行的追蹤
SELECT * FROM sys.traces
WHERE is_default <> 1

---

參考資料：
sp_trace_create (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms190362%28SQL.90%29.aspx

sys.traces (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms178579%28SQL.90%29.aspx

追蹤屬性 (一般索引標籤)
http://technet.microsoft.com/zh-tw/library/ms178910.aspx