使用Trace Management Object監測和診斷SQL Server（一）

大家一定用過Profiler工具，我們可以用它來對SQL Server建立trace來監測某些感興趣的事件，也可以replay抓到的trace來診斷是哪些SQL語句的執行造成你的SQL Server耗費了大量的CPU資源。但Profiler是個GUI程式，有沒有辦法通過程式來抓trace和重放trace呢？也許有些讀者會想到用SQLCMD.exe執行sp_trace_create等儲存過程來操作，但那畢竟還是有些麻煩，這裡我們要介紹的Trace Management Object（TMO）則是.NET物件，你可以把它理解成trace/replay的API，你可以非常方便地在你的.NET程式中使用。請注意由於SQL Express版本不支援trace，因而TMO物件也無法在SQL Express版本上執行，即使是SQL Server 2008 Express with Advanced Services也不支援。

在SQL Server 2005裡TMO物件被實現在了Microsoft.SqlServer.ConnectionInfo.dll裡，在SQL Server 2008裡TMO物件則被移到了Microsoft.SqlServer.ConnectionInfoExtended.dll裡，但仍然在Microsoft.SqlServer.Management.Trace名稱空間裡。下面我們將以SQL Server 2008為例。

用VS2005新建一個Visual C#的Console Application工程，在Project選單裡點選Add Reference…增加對下表幾個元件的引用：

元件	描述
Microsoft.SqlServer.ConnectionInfo	需要SqlServerInfo類來建立對SQL的連線
Microsoft.SqlServer.ConnectionInfoExtended	TMO物件在這個Assembly裡
Micrososft.SqlServer.Management.Sdk.Sfc	SQL Server 2008裡很多元件是基於它建立，所以必須增加這個引用

元件描述Microsoft.SqlServer.ConnectionInfo 需要SqlServerInfo類來建立對SQL的連線 Microsoft.SqlServer.ConnectionInfoExtended TMO物件在這個Assembly裡 Micrososft.SqlServer.Management.Sdk.Sfc SQL Server 2008裡很多元件是基於它建立，所以必須增加這個引用

http://www.codeplex.com/上SQL Server 2008的Samples裡有個Readme_Tracer的例子（http://www.codeplex.com/MSFTEngProdSamples/Release/ProjectReleases.aspx?ReleaseId=18651），這個例子使用Standard.tdf模板啟動一個live trace，trace的內容將列印在Console視窗上，但把結果列印到Console視窗上非常亂，而且也沒有太大的實用價值，大家有興趣可以去參考一下。本文第一部分將介紹一個capture trace的示例，和Readme_Tracer有點類似，但我們會把trace結果輸出到trace檔案，第二部分將介紹一個replay trace的示例，這也是Profiler最常用的兩個功能。

Capture trace示例

這個例子模仿你使用Profiler工具監測SQL Server操作的過程，程式啟動一個trace，將抓到的trace event輸出到檔案中，等待60秒後退出。讀者可以嘗試將TraceFile類改為TraceTable類來輸出到資料庫表中。下面是詳細的步驟和描述：

1、TraceServer類代表連線到SQL Server Instance的一個trace，下面的程式碼演示瞭如何建立一個TraceServer物件。程式碼前兩行使用Windows認證方式建立一個SqlConnectionInfo物件，你也可以通過提供使用者名稱/密碼的方式建立這個物件，InitializeAsReader函式的第二個引數是trace模板，這裡使用的TSQL_Replay模板，你需要根據你的SQL Server安裝目錄進行修改。

SqlConnectionInfo connInfo = new SqlConnectionInfo(".");
connInfo.UseIntegratedSecurity = true;
TraceServer traceServer = new TraceServer();
traceServer.InitializeAsReader(connInfo, @"C:\Program Files\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\TSQL_Replay.tdf"); 

2、TraceFile類代表一個trace檔案，它既可以是capture trace的輸出檔案，也可以是replay trace的輸入檔案，下面的程式碼將traceFile物件設為traceServer所代表的trace的輸出檔案，最後一行為traceFile增加了一個WriteNotify事件的event handler，通過它我們將對輸出做一些過濾。

TraceFile traceFile = new TraceFile();
traceFile.InitializeAsWriter(traceServer, @"d:\tracefile.trc");
traceFile.WriteNotify += new WriteNotifyEventHandler(WriteHandler); 

3、這兩個物件建立完畢，此時trace已經開始了，但tracefile.trc卻始終是0位元組，為什麼呢？因為你還需要呼叫TraceFile類的Write函式來輸出，但Write函式有兩個問題，一是呼叫一次只輸出一個trace event，你需要不停地呼叫它；二是Write函式是同步的，如果沒有可以輸出的內容的話它會阻塞，所以你需要另起一個執行緒來呼叫Write函式。下面的程式碼啟動WriteTraceProc執行緒並在60秒後結束capture trace。請注意traceServer.Close()必須在thread.Join()之前呼叫，否則WriteTraceProc執行緒可能會一直阻塞在Write函式呼叫上。

Thread thread = new Thread(WriteTraceProc);
thread.Start(traceFile); //pass traceFile as parameter
Thread.Sleep(60000);
lock (flagLock)
{
    exitFlag = true;
}
traceServer.Close();
thread.Join();
traceFile.Close(); 

4、接著是flagLock，exitFlag的定義及WriteTraceProc的程式碼，WriteTraceProc將持續呼叫Write直到exitFlag被主執行緒置為true。

private static object flagLock = new object();
private static bool exitFlag = false;
private static void WriteTraceProc(object obj)
{
    TraceFile traceFile = (TraceFile)obj;
    while (true)
    {
        lock (flagLock)
        {
            if (exitFlag)
                break;
        }
        traceFile.Write();
    }
}

5、最後是WriteHandler的程式碼，我們將使用EventClass列來過濾所有Audit Login及Audit Logout事件，你可以根據需要設定你的過濾條件。由於Books Online沒有提供詳細的文件，你也許需要使用IDataRecordChanger介面的GetName()來列舉所有你能使用的列。

private static void WriteHandler(object sender, TraceEventArgs args)
{
    IDataRecordChanger recordChanger = args.CurrentRecord;
    string eventClass = (string)recordChanger["EventClass"];
    if (eventClass.StartsWith("Audit"))
        args.SkipRecord = true;
} 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-604314/，如需轉載，請註明出處，否則將追究法律責任。