Replaying Trace Data

To replay a trace, you must have a trace saved to a file or a table. You can define a trace to be saved when you create or modify the trace definition. You can also save the current contents of the trace window to a file or table using the Save As Trace File or Save As Trace Table options in the File menu.

To replay a saved trace, you can use the File, Open menu that allows you to open a trace file or a trace table. After you select the type of trace to replay, a grid with the trace columns selected in the original trace will be displayed. At this point, you can start the replay of the trace, either step by step or complete execution of the entire trace. Either option will display the SQL Server Connection dialog box that allows you to connect to an instance of SQL Server. After you are connected, you will be given replay options as shown in Figure 7.6.

Figure 7.6. Options for replaying a Profiler trace.


The first option, which is enabled by default, replays the trace in the same order that it was captured and allows for debugging. The second option takes advantage of multiple threads. It optimizes performance but disables debugging. A third option involves specifying whether to display the replay results. You would normally want to see the results, but for large trace executions, you might want to forgo displaying the results and send them to an output file instead.

If you choose the option that allows for debugging, you will be able to execute the trace in a manner similar to many programming tools. You can set breakpoints, step through statements one at a time, or you can position the cursor on a statement within the trace and execute the statements from the beginning of the trace to the cursor position.


Automating testing scripts is another important use of the SQL Profiler Save and Replay options. For instance, a trace of a heavy production load can be saved and rerun against a new release of the database to ensure that the new release has similar or improved performance characteristics and returns the same data results. The saved traces can help make regression testing much easier.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features