Trace and Replay Objects: A New API for SQL Server Tracing and Replay

Slavik Krassovsky
Microsoft Corporation

December 2004

Applies To:
   Microsoft SQL Server 2005

Summary: This paper introduces Trace and Replay objects, a new feature in Microsoft SQL Server 2005. Trace and Replay objects is a new managed API for tracing, trace manipulation, and trace replay. (12 printed pages)

Contents

Introduction
Trace Objects
Replay Objects
Conclusion

Introduction

This paper is written for users who deal with SQL Profiler or collect Microsoft SQL Server traces and want to automate trace collection, trace manipulation, and trace replay.

Knowledge of the C# language and some experience with the SQL Profiler tool is required to fully take advantage of the information provided.

SQL Server trace is a mechanism for monitoring and recording activity inside SQL Server. Essentially, when any activity occurs (a query is sent against the instance of SQL Server, for example), a special entity called an event is generated inside the server. The event is then shipped to a monitoring tool called SQL Profiler, which displays this event and all its attributes (called columns).

This combination of SQL Trace and SQL Profiler has proven to be a very powerful performance analyzing and tuning tool—it enables users to see what activity a particular application generates and what impact it has on the server.

However, the SQL Profiler tool is completely manual; therefore, it is not easy to automate the creation of the trace or perform trace analysis and trace manipulation. For most of these tasks, users must start SQL Profiler and use it interactively.

Trace and Replay objects, a new feature in Microsoft SQL Server 2005 that offers a new managed API for tracing and trace replay, are introduced to enable automation.

Trace and Replay objects simplify the management of SQL Server in the following ways:

• First, they enable users to completely automate tuning, security audits, and health monitoring of traced servers.
• Second, they enable automatic functional verification of new server releases and security packs.
• Third, they provide users with the ability to establish performance benchmarks against current server performance.

This paper discusses Trace objects and Replay objects in detail.

Trace Objects

Organizations where the tracing of SQL Servers is performed on a regular basis often have to deal with large amounts of trace data, which needs to be processed and analyzed. SQL Profiler provides features to filter the traces; such an approach can be extremely labor-intensive, however. The ability to create a small program that would perform analysis in an automated fashion would provide a better way to handle this. For example, such a program could crawl through trace data and identify the most frequently executed query on the server. Such a program could be reused and perhaps later expanded to include increasingly sophisticated analysis logic.

Another important aspect of automation is a timely reaction to events that are happening on the server. It would be convenient to have a program that could start a trace, monitor events of the trace, and perhaps alert the administrator if the duration of particular queries exceeds a certain threshold.

It would also be convenient to be able to implement a way to perform other tasks, like moving trace files into SQL Server tables, or from tables to files, in a customized fashion.

Trace Object technology can help address these and other problems.

Trace objects serve the purpose of starting a new trace, and of reading and writing trace log files and trace tables. Essentially, there are three classes for accomplishing these purposes:

• TraceServer—Starts and reads a new trace.
• TraceFile—Reads and writes to a trace file.
• TraceTable—Reads and writes to a trace table.

These classes belong to the Microsoft.SqlServer.Management.Trace namespace and provide an abstraction of a stream of trace events. This stream could be read-only (TraceServer) or provide reading and writing capabilities (TraceFile and TraceTable). Figure 1 illustrates the relationships between these classes.

Figure 1. Microsoft.SqlServer.Management.Trace namespace

Example 1: How to Start a Trace

To gain a better understanding of Trace objects, here's an example of how to construct a simple C# program that starts a trace and prints the names of the first 10 events:

1. Create a TraceServer object:

   TraceServer reader = new TraceServer();
   

2. Create an object that is responsible for storing connection information:

   ConnectionInfoBase ci = new SqlConnectionInfo("localhost");
   ((SqlConnectionInfo)ci).UseIntegratedSecurity = true;
   

3. Pass connection information to the InitializeAsReader() method of the TraceServer class as a first argument. The second argument is the name of the trace template file. You can create such a template file by using the SQL Profiler tool, or use stock templates that are shipped with SQL Server 2005 (templates are located in the \Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates folder):

   reader.InitializeAsReader(ci, @"Standard.tdf");
   

4. Declare an event counter:

   int eventNumber = 0;
   

5. Continue reading in the loop and incrementing a counter until 10 events have printed:

   while (reader.Read())
   {
   Console.Write( reader.GetValue(0).ToString() );
   eventNumber ++;
   if (eventNumber == 10) break;
   }
   

6. Close the reader:

   reader.Close();       
   

The complete program looks as follows:

// need to reference
// %Program Files$\Microsoft SQL
// Server\90\SDK\Assemblies\
// Microsoft.SqlServer.ConnectionInfo.dll 

public void FileToTable()
{
    TraceServer reader = new TraceServer();

    ConnectionInfoBase ci = new SqlConnectionInfo("localhost");
    ((SqlConnectionInfo)ci).UseIntegratedSecurity = true;
            
    reader.InitializeAsReader(ci, @"Standard.tdf");

    int eventNumber = 0;

    while (reader.Read())
    {
        Console.Write( "{0}\n", reader.GetValue(0).ToString() );
        eventNumber ++;
        if (eventNumber == 10) break;
    }
    reader.Close();        
}

The same code is applicable to starting a trace against Analysis Services. The only difference is in the ConnectionInfo object. Instead of SqlConnectionInfo, OlapConnectionInfo should be created:

OlapConnectionInfo connection = new OlapConnectionInfo();
connection.ServerName = "localhost";

Note   The reader.Read() call is synchronous, meaning that it will not return until the event is read or the trace is stopped. To stop a trace, you need to call TraceServer.Stop() from a separate thread.

Example 2: FileToTable Utility

The following sample is a simple utility that converts data that is stored in a file to a table. The advantage of using this utility over the Transact-SQL fn_trace_gettable function is that this utility can read Microsoft SQL Server 7.0 and SQL Server 2000 files, and it can modify the data "as it goes."

// need to reference
// %Program Files$\Microsoft SQL
// Server\90\SDK\Assemblies\
// Microsoft.SqlServer.ConnectionInfo.dll 

//Main routine    
public void FileToTable()
{
    SqlConnectionInfo connInfo = new SqlConnectionInfo("localhost");
    connInfo.DatabaseName = "master";

    TraceFile  reader = new TraceFile();
    reader.InitializeAsReader("input.trc");

    TraceTable writer = new TraceTable();
    writer.InitializeAsWriter(reader, connInfo, "SampleOutputTable");

    writer.WriteNotify += new WriteNotifyDelegate(OnWriteNotify);

    while( writer.Write() ){};
    writer.Close();
}

// Event handler gets invoked before writing of every row
private void OnWriteNotify( IDataRecordChanger currentRecord, 
    out bool skipRecord )
{
    currentRecord.SetValue(1, "New Data");
    skipRecord = false;
}

The preceding utility is provided as an example. With moderate modifications, you can change this utility to become a Table-to-File converter as shown in the following example:

1. The main routine in this utility is called FileToTable(). Start this routine by creating the SqlConnectionInfo object:

   SqlConnectionInfo connInfo = new SqlConnectionInfo("localhost");
   connInfo.DatabaseName = "master";
   

   For more information about that class, see SQL Server Books Online for SQL Server 2005. This class basically provides connection information to the server and the database where the table will be stored (master in this case).

2. Use calls to create and initialize the TraceReader class:

   TraceFile  reader = new TraceFile();
   reader.InitializeAsReader("input.trc");
   

   Note   The input.trc filename is provided here. The full path is also acceptable.

3. Create and initialize the table writer object:

   TraceTable writer = new TraceTable();
   writer.InitializeAsWriter(reader, connInfo, "SampleOutputTable");
   

   The table name is SampleOutputTable. The server connection information is provided earlier in Step 1.

4. Set up the OnWriteNotify event handler:

   writer.WriteNotify += new WriteNotifyDelegate(OnWriteNotify);
   

   This event handler will be invoked on every write the writer performs.

5. Sit in the loop and write:

   while( writer.Write() ){};
   

6. As writing is done, close the writer (which closes the reader as well):

   writer.Close();
   

Through the IDataRecordChanger interface, the event handler can examine the record (by way of the IDataReader interface from which the IDataRecordChanger inherits the record) and change the values of the columns. In this case, the column is changed with index 1 and the value is set to the string "New Data":

currentRecord.SetValue(1, "New Data");
skipRecord = false; 

Trace Objects Advanced

This section discusses how Trace objects are built in more detail. This information is helpful, but not essential for understanding how to use the objects. Figure 2 illustrates the Trace object hierarchy.

Figure 2. Trace object hierarchy

The main interface for accessing data is the .NET Framework interface called IDataRecord from the System.Data namespace. It allows users to access a row of data at a time—a row consists of columns that can be examined by index or name. (For more information, see the .NET Framework documentation).

The IDataRecord interface is extended to IDataReader, which is also a standard .NET Framework interface. The most important thing IDataReader adds is Read() methods, which allow record navigation in sequential read-only forward-only fashion.

The methods associated with IDataReader are implemented in the abstract class TraceReader, which provides reading capabilities that all three classes (TraceServer, TraceTable, and TraceFile) inherit. TraceTable and TraceFile also possess writing capabilities that are implemented in the TraceReaderWriter class, which extends the TraceReader class.

TraceServer, TraceTable, and TraceFile, in addition to the capabilities discussed earlier, provide the following initialization methods:

• TraceServer:

  InitializeAsReader (ConnectionInfoBase serverConnInfo, String profileFileName)

  • serverConnInfo: Provides connection information to the server that will be traced.
  • profileFileName: A path to a file that contains a SQL Profiler trace profile. (For more information about how to create one, see SQL Server Books Online.)

• TraceTable:

  InitializeAsReader (ConnectionInfoBase serverConnInfo, String tableName, String tableOwner)

  • serverConnInfo: Provides connection information to the server that contains trace table.
  • tableName: The name of the table.
  • tableOwner: The schema to which the table belongs.

  InitializeAsWriter (TraceReader source, ConnectionInfoBase serverConnInfo, String destinationTableName)

  • source: A TraceReader object that contains trace data (could be TraceFile or TraceTable)
  • serverConnInfo: Provides connection information to the server that contains trace table.
  • destinationTableName: The name of the table to which the trace data is written.

• TraceFile:

  InitializeAsReader (String fileName)

  • Filename: The name of the file that contains trace data.

  InitializeAsWriter (TraceReader source, String destinationFileName)

  • source: A TraceReader object that contains trace data (could be TraceFile or TraceTable).

  • destinationFileName: The name of the file to which the trace data is written.

    Note   TraceFile and TraceTable also have InitializeAsReplayOutputWriter() methods, which will be discussed later in this paper.

Replay Objects

Replay objects provide functionality to replay a trace against an instance of SQL Server, thus simulating the load that occurred on the server at the time the trace was taken. This technology is built on top of the SQL Profiler replay that now is being made accessible by means of the programmatic interface.

Replay objects contain two classes: the TraceReplay class, which implements all the replay functionality, and the TraceReplayOptions helper class, which helps configure the TraceReplay object.

The TraceReplay class provides the following methods:

• Start()—Starts replay.
• Pause()—Pauses replay.
• Stop()—Stops replay.

The TraceReplay class provides the following properties:

• Source—a mandatory TraceReader object that contains the trace to be replayed.
• OutputFile—an optional file to which the output will be streamed.
• OutputTable—an optional table to which the output will be streamed.
• Connection—mandatory Connection information objects. TraceReplay will replay the trace against the server that is specified in this connection object.
• Options—configuration options (see below).

The TraceReplay class provides the following events:

• OnReplayStart—a handler is invoked on Replay Start.
• OnReplayPause—a handler is invoked on Replay Pause.
• OnReplayStop—a handler is invoked on Replay Stop.
• OnReplayEvent—a handler is invoked before replaying an event.
• OnReplayResultEvent—a handler is invoked on every result of repayable event.

TraceReplayOptions provides the following configuration options:

• NumberOfReplayThreads—the number of threads Replay employs.
• HealthMonitorPollInterval—how often the health monitor wakes up and examines stale threads.
• HealthMonitorWaitInterval—the stale thread maximum time to live.
• Mode—the mode of replay (either Sequential or connection level).
• KeepResults—specifies whether to keep the replay results.

Example 1: FileReplay Utility

// need to reference
// %Program Files$\Microsoft SQL
// Server\90\SDK\Assemblies\
// Microsoft.SqlServer.ConnectionInfo.dll 

public void TestReplay_SQL_File()
{
    TraceReplay replay = new TraceReplay();
    TraceFile source = new TraceFile();
    source.InitializeAsReader("input.trc");
    replay.Source = source;

    SqlConnectionInfo connection = new SqlConnectionInfo();
    connection.ServerName = "localhost";
    connection.UseIntegratedSecurity = true;
    replay.Connection = connection;

    replay.OnReplayEvent += 
    new TraceReplay.OnReplayEventHandler(OnReplayEvent);

    replay.Start();
}

public static void OnReplayEvent( int recordNumber, IDataRecordChanger currentRecord, out bool skipRecord )
{
    skipRecord = false;
    currentRecord.SetValue(2, 2); //setting database id to the new value
}

1. The main routine is called TestReplay_SQL_File(). Start this routine by creating the TraceReplay object:

          TraceReplay replay = new TraceReplay();
   

2. Use calls to create and initialize the FileReader class that will be used as a source of the replay:

          TraceFile source = new TraceFile();
          source.InitializeAsReader("input.trc");
          replay.Source = source;
   

   Note   The input.trc filename is provided here. The full path is also acceptable.

3. Create and configure connection information by using the SqlConnectionInfo class:

   SqlConnectionInfo connection = new SqlConnectionInfo();
   connection.ServerName = "localhost";
   connection.UseIntegratedSecurity = true;
   replay.Connection = connection;
   

4. Set up the OnReplay event handler:

   replay.OnReplayEvent += new TraceReplay.OnReplayEventHandler(OnReplayEvent);
   

5. Start Replay:

   replay.Start();
   

   Note   The start method does not return until Replay is finished; if you need to stop or pause a replay in progress, use a separate thread.

Again, the IDataRecordChanger interface not only provides a way to examine the record (by way of the IDataReader interface from which the IDataRecordChanger inherits the record) but also changes the values of the columns. In this case the column is changed with index 2 and the value is set to 2:

skipRecord = false;
currentRecord.SetValue(2, 2); //setting database ID to the new value

Conclusion

The Trace and Replay objects technology takes advantage of the Microsoft .NET platform that is now integrated with SQL Server 2005 to enable its users to perform routine tracing and replay tasks in automated fashion.

The unique features of the Trace and Replay objects enable users to easily manipulate tracing, read and write to trace files and tables, and replay traces. Optionally, users can also modify the data in trace files by using writers' event handlers.

The scenarios of usage can be analysis of large trace file logs, live monitoring tasks, converting or altering trace data, and automated replay.