SQL Server Profiler Save and replay trace

The role of replay tracking

You can use a trace as a test tool, and when you call some stored procedures in the correct order, you will be able to regenerate specific failures.

Tracking templates

There are four options for saving a trace (file-to-Save As)

1. The trace files option is used to save the data in a binary format file, which is the fastest, and the least disk-byte-consuming method.

2. The trace table option is used to save the data to a new or pre-created table in the user-selected database. Applies to the applicable T-SQL operations or report data.

3. The trace XML file option saves the data in an XML-formatted file.

4. The replay trace XML file option saves the data to an XML text file, but only those events or columns that are required by the replay function are saved.

How to create a replay trace:

1. In the SSMs window, select SQL Server Profiler from the tool, create a new trace in Profiler, and select Tsql-replay in the Use template option:

2. Execute the run start trace, and then in SSMs new three query Windows execute the statement in sequence select 1; select 2; Select 3:

3. Go back to the profiler's trace window to see the execution results, and through eventsequence you can see the order in which the statements are executed.

4. Stop the trace, and then save the trace (file, save as, trace file), close the current trace window, and then reopen the saved trace file. You can see that the options under the Replay menu become optional.

5. Select Start in the Replay menu, there are two ways to replay options, one is to replay sequentially, and the other is multithreaded replay.

6. Select replay in event order to find that events are performed in the same order, which is useful for special cases occurring during replication tracking, such as when debugging a deadlock or group race that results from a specific interaction between multiple threads accessing the same data at the same time.

7. Select multi-threaded replay, you can find that the event is not executed in the original order, because it is executed by multiple threads concurrently, it is advantageous to replay a large number of independent SPID between the tracking data.

8. Set breakpoints on replay to compare a small amount of data that is suitable for tracking large amounts of data.

SQL Server Profiler Save and replay trace