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

http://msdn.microsoft.com/en-us/library/ms345134(SQL.90).aspx

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.