- Profiler Trace Contents
- Profiler Templates
- Saving and Examining Trace Output
- Replaying Previously Recorded Traces
- Recording Traces Without Profiler
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Replaying Previously Recorded Traces
You might want to replay SQL statements recorded in your trace on the same or different server for a couple of reasons. First, you might want to see if the performance problem is resolved after you have tuned your indexes or modified a stored procedure. Second, you could use a trace for load-testing to see if your application can deliver acceptable performance on a previously unused piece of hardware.
In order to replay a trace, you must capture certain events and data columns (along with others that aren’t required). The events required for replaying a trace are:
- Cursors: Cursor Execute
- Cursors: Cursor Open
- Cursors: Cursor Prepare
- Sessions: Existing Connection
- Security Audit: Login
- Security Audit: Logout
- Stored Procedures: RPC: Starting
- TSQL: Execute Prepared SQL
- TSQL: Prepare SQL
- SQL: Batch Starting
Data columns necessary for replaying traces include:
- Event Class (required by all traces)
- Event Sub Class
- Application Name
- SPID (required by all traces)
- Database ID
- Binary Data
- Text Data
- Host Name
- Server Name
- Start Time
When you load a trace that can be replayed, the Replay toolbar buttons and Replay menu become active within Profiler’s user interface, as shown in Figure 2.
)
Figure 2 When you load a trace that can be replayed, the Replay toolbar becomes available.
The Replay button allows you to replay the entire trace at once, whereas the Step option allows you to run one command at a time. You also have an option called Run to Cursor which executes all statements from the beginning of the trace to the statement that you have highlighted within the trace. You can also set breakpoints by choosing Replay, Toggle break-point or clicking the icon that looks like a hand after highlighting the statement you wish to use as a breakpoint. Breakpoints are a convenient way to stop and resume trace execution as needed.
You must specify the server where you want your trace SQL statements to be replayed as well as the credentials for connecting to the server. Once you specify connection credentials, Profiler presents you with the dialog shown in Figure 3.
)
Figure 3 The Replay Options dialog box.
This dialog allows you to change the server you wish to replay the events to, specify replay options, and specify whether you want to save output into a file. You can save the output of a trace replay into another trace file that you can examine later. Replay options allow you to:
- Debug the events and execute all commands serially in the order they were recorded.
- Replay events in parallel on multiple threads. With this option, you can simulate the load on the server, where the events are replayed but cannot debug the trace output.
When you debug SQL statements with trace replay, the Profiler user interface has three panes. The top pane shows you events and data columns, the middle one displays commands that are being executed, and the bottom pane shows the results of the statement as shown in Figure 4.
)
Figure 4 When you debug SQL statements with trace replay, the Profiler user interface has three panes.
Debugging can be helpful for determining the number of rows that are sent back to the user when a particular statement is being executed. If you see thousands of "replay result row" for each execution of your procedure, it should be apparent that you’re not filtering your queries appropriately—unless your procedure is generating a huge report that will be printed for further analysis. Also notice that "Replay Result Set" event (in the middle pane) shows you the columns that will be available in the output, whereas "Replay Result Row" shows you a single row of the query’s output.
There are a couple of gotcha’s you need to be aware of with replaying traces. First, notice that you must record the database identifier in order to be able to replay the trace. If you replay the trace on the same server as the server where you recorded the trace all will be well. However, if you attempt replaying the same trace on a different server, the database identifier might not be the same. System databases such as master, tempdb, MSDB, and model have the same identifiers on all servers. User databases, however, won’t have the same IDs. Therefore, if you need to replay the trace recorded on a user database, you need to make sure that trace source and destination servers have the same identifiers for the database of interest.
Second, if you run the trace using multiple threads, you might notice that trace will execute a few commands and hang—what’s the deal? Some of the commands within your trace might lock resources needed by other commands. So, if you examine the current activity on your server while replaying the trace on multiple threads, you might notice blocking. If this is the case, you must either stop the replay or kill the offending connection to continue replaying the trace.