See Also: Main_Page - Monitoring & Tuning - Tuning Tools - SQL Server Profiler

You may wish to replay previously recorded traces on the same or a different server. By replaying the trace you can examine individual SQL statements and stored procedures and see what parameters are being passed in. Doing so will let you analyze performance of the same routine with different parameters or variables. In addition, you may replay the trace on a different server to see if different hardware or different SQL Server configuration options allow your statements to perform better. Another good use for replaying traces is when you want to load test changes to your application.

• 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

• 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

If you haven't replayed traces before and want to attempt doing so it's probably best to get started with the built-in Replay template provided with SQL Server.

Once you have a trace file or trace table you wish to replay you can open it by choosing File --> Open --> Trace Table or File --> Open --> Trace File. If you choose to open a trace file, you will get a dialog that helps you select the desired trace file. If you choose to open a trace table, you'll have to provide a valid login for connecting to the server where the trace table is stored. Then you can choose from previously saved trace table(s).

When you load a trace file or table to replay the appearance of the Profiler changes slightly - the replay menu and toolbar becomes active. From the Replay menu you can choose Start, Step or Run to Cursor. The Start option simply executes the entire trace; Step allows you to step through the trace one statement at a time, whereas Run to Cursor executes a number of statements from the beginning of the trace to where the cursor is pointing. Regardless of which option you choose you must specify which server you wish the trace to run on and provide a valid login for that server.

Once you are connected to the server you can choose how you wish your trace to be executed - on one thread or on multiple threads. The former enables debugging SQL statements, including viewing the output of their execution. The latter optimizes performance, but disables debugging. When debugging is turned on, the Profiler interface has three panes instead of two (as it does when recording a trace). The top pane displays the events and text, middle contains statements that are being executed and the bottom contains the results. The Replay menu also allows you to set breakpoints and then run multiple statements by selecting the Run to Cursor option. You are also allowed to pause or stop the trace while it is being replayed.

There are a couple of things to keep in mind when replaying traces on a server other than the one where the trace was recorded. First, unless you're tracing activity on system databases, such as MSDB or master, the database identifiers might not be the same. For example, suppose you record a trace on ServerA using database TestDB. Your trace shows the database ID of 12. When you replay the trace on ServerB you get a bunch of errors. What's the problem? Well, the database TestDB which you wish to replay the trace on has an identifier of 11 in the sysdatabases system table, therefore the Profiler cannot find the database objects you wish to work with because it attempts connecting with a database ID of 12.

Second, recall that you must be a member of the SYSADMIN fixed server role to record a trace. But you do NOT have to use a SYSADMIN login to replay a trace. Keep that fact in mind since you might wish to replay trace statements with the same login as they were executed on the production server. For example, if you have a user JohnDoe in production (who is not an administrator) you might wish to replay the trace on the test server also under JohnDoe's account.

Finally, if you choose to use multiple threads for replaying the trace the Profiler might open dozens of connections in order to optimize performance. However, some of these connections spun off by the Profiler might block each other's queries. When trace is being replayed, the Profiler keeps track of progress by providing you with the percentage of the trace that has been replayed. If you see the progress getting stuck for a while, there is a good chance that Profiler connections are blocking each other. If you wish to continue replaying the trace events you'll have to disconnect the offending session using the KILL command.