A Guide To Building and Running A Replayable Trace File In SQL Server

Running a SQL Trace in SQL Server is a great way to get a snapshot of the activity occurring in a database at any given time.  It can be used to determine and analyze what procedure is being called or capture activity for a particular user or users.  There are a host of configuration options within both SQL Profiler and when running a trace manually through a script.

One often overlooked feature of SQL tracing is the ability to create a replayable trace.  A replayable trace allows you to capture the activity on one server and, using the saved trace file(s), replay it on another server.  This can be used for debugging or if you want to test a particular data load on a different server.

In a recent case I ran a 2 hour replayable trace against a production database running on SQL Server 2012 and restored a recent backup of that database to a SQL Server 2016 environment.  I then replayed the trace to get a better understanding of any issues that might arise if we were to upgrade to SQL Server 2016.  Though this was just a first step in the overall analysis of that project, it gave me a huge jump start on knowing how tedious an upgrade might be.

There are two solid methods for running a replayable trace.  First, you can run it within SQL Server Profiler using the TSQL_Replay template.  The screenshot below demonstrates this.

As you can see I have given the trace a name and updated the template being used to show TSQL_Replay.  I also chose to save the trace as a file to my H:\ drive.

It is also important to make sure you set the max file size for each trace file.  In the screenshot above I have it set to 100MB.  If exceeded, a new trace file will be created.  If the total size of the trace was 350MB I would have three 100MB files and a single 50MB file for a total of 4 files.

I’m not a fan of using SQL Server Profiler to run large production traces. 

With that said, I will now discuss the second method for running a replayable trace in SQL Server.   Though it may appear slightly more complicated, it is much less resource intensive than SQL Profiler and provides the same level of customization.  

To begin with you’ll need to copy/paste the following dbo.runtrace stored procedure into SQL Server Management Studio.  Don’t execute the script quite yet as you’ll want to make some changes before pushing it to your database.  The procedure is essentially what SQL Server Profiler calls when running a trace.  There are a host of parameters provided which can be used to further customize your trace.  For the purposes of this post I will only be focusing on a few of these options.

Once you’ve copied the contents of the script to a query window you’ll want to take note and likely modify several items before pushing the procedure to the database.  Though this is not technically necessary as you can pass new parameters to the script when calling it, it’s good to set a baseline so you won’t need to pass as many parameters to the procedure when calling it. 

The first item you may want to change will be the very first line of the script.  USE [master] will push the procedure to the master system database.  I prefer to update this to publish the procedure to the desired user database.

The next piece of the script that you will want to modify concerns the file path location.  At the top of the script where the CREATE PROCEDURE code resides you will see the parameter @trace_file_folder being called.  In the example above it is set to S:\TraceFiles.  You will want to modify this to write to a local or network drive available to your server.

Next you’ll want to update the @trace_runtime_in_min parameter to specify how long the trace should run.  This part of the script is directly below the CREATE PROCEDURE piece.  By default it is set to 120 minutes.

Finally you’ll want to update the @maxfilesize parameter.  By default this is set to 100.  As before, when configuring the trace in SQL Profiler, you can set the max size of each trace file.  The parameter for setting this is located about a quarter of the way down the script.

Once you’ve made the necessary changes to the script you can publish it to the database.  

When you’re ready to call the procedure you can use the following script to do so:

The first thing you’ll notice are some of the parameters already match what is in the procedure.  Technically speaking, if they are the same, then they do not actually need to be included in the execute statement.  However, when calling the procedure I have changed the @trace_file_folder to reference the G:\ drive instead of the S:\ drive specified in the procedure.  This will reassign the file path for this execution of the procedure.   I have also updated the @trace_runtime_in_min to only run for 5 minute instead of 2 hours.

The most important piece of the script is concerning the @replayable parameter.  By default, within the procedure, this is set to 0.  Since we want to be able to replay the trace this must be set to 1 prior to executing the script.

Once your have executed dbo.runtrace with the desired parameters you will see the following output.  Take note of the last column which will indicate when the trace will be complete.

The next step is to replay the trace file against a database. 

In order to do this you’ll need to open SQL Server Profiler and connect to the server from which you want to replay the trace events.

Once connected navigate to File>>Open>>Trace File as shown below.

Due to certain permission issues on certain servers, you may need to copy the trace file(s) to a new location.  A common error message you may see when opening the trace files directly from the folder to which they were saved is shown below:

Even though I am a domain admin and sysadmin on this server, the file I am trying to open is essentially locked.  To resolve I simply copied the folder containing the trace files to my desktop and opened them from there.

Once the trace file has been loaded into Profiler, you’ll notice you now have access to the Replay option on the menu, which would have previously been grayed out.

Select Replay from the menu and click Start.

The Replay Configuration window will be displayed.  On the Basic Replay Options tab you can save the results to either a file or an existing table in the database.  For this example I will be saving to a file on my desktop named Replay_Test.trc.  I also recommend checking the box next to Display execution time.  This will display the total execution time for each event as the trace is being replayed on the server.  I find this to be one of the more useful fields when comparing stats between servers and am not sure why it’s not checked by default.

Though I have made no changes to the default settings regarding the Number of replay threads, its important to understand this setting.  This allows you to specify the number of replay threads that will be used concurrently.  A higher number of threads will consume more resources during the replay but will speed up the overall time it takes for the replay to complete.  It’s important to note that event ordering is not a factor when multiple threads are being used.

Now click on the Advanced Replay Options tab. Review the options here.  The screenshot below shows the default setting for this tab.  Though I will not be making any changes to this tab, I find the most useful feature here to be the option to Enable SQL Server blocked processes monitor.  For certain performance analysis traces this can be used to set how often the blocked processes monitor searches for blocking in the database.  This can be useful when combined with a replayable trace as you will essentially be replaying the blocking events.

Once you have made the necessary configuration changes click OK to start the replay.

While the replay is running you will see a secondary pane open below the original trace file that shows the results of the replay.  While the replay is running, you will also see a marker moving through the original trace which indicates which statements are currently being executed.  The replay shouldn’t take nearly as long as the original trace as it will execute each statement immediately after the previous one completes.

When the replay is finished you can then go through the results and analyze it for any errors and check the execution times.  The final record in the replay results will include the Replay Statistics Events  output.

This will sum up the results of the replay and provide you with some valuable insight regarding how the trace replayed on the server.

Replayable traces can be very useful when diagnosing a variety of performance issues in SQL Server.  They can also be used to test how activity on the server might be affected by an upgrade. For example, by simply capturing a replayalbe trace on a SQL Server 2012 environment and then replaying it against a 2016 environment you can easily test to see if any obvious issues might arise.  Though this would only be a starting point in upgrade testing, it can certainly highlight any major issues you might encounter as well as provide you with some useful statistics regarding the upgraded environment.