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 introducesTraceAndReplayObjects, a new feature in Microsoft SQL Server 2005.TraceAndReplayObjects 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. essential, 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 participant 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.

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

TraceAndReplayObjects 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 discussesTraceObjects andReplayObjects in detail.

Trace objects

Organizations where the tracing of SQL servers is stored med 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 wowould perform analysis in an automatic fashion wowould provide a better way to handle this. for example, such a program cocould crawl through trace data and identify the most frequently executed query on the server. such a program cocould 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 wocould be convenient to have a program that cocould start a trace, monitor events of the trace, and perhaps alert the administrator if the duration of the specified queries exceeds a certain threshold.

It wocould 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.

TraceObjects serve the purpose of starting a new trace, and of reading and writing trace log files and trace tables. Essential, 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 toMicrosoft. sqlserver. Management. TraceNamespace and provide an alternative action of a stream of Trace events. This stream cocould be read-only (Traceserver) Or provide reading and writing capabilities (TracefileAndTracetable). Figure 1 into strates the relationships between these classes.