搜尋本站文章

2010-05-23

使用 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