Using the SQL Server Distributed Replay feature

By: Ben Snaidero   |   Updated: 2013-11-01   |   Comments (1)   |   Related: > Profiler and Trace

Problem

If you read my previous tip on Configuring the SQL Server Distributed Replay feature you should been able to successfully configure this feature. This tip will guide you through a simple example to illustrate how you can use this feature to replay a simple SQL Server trace file that contained two sessions executing statements concurrently.

Solution

Capturing the SQL Trace

At the end of our last tip we had completed configuring all the components and started the required windows services so now all that we need to get started is to create a trace file that has a couple of sessions executing statements concurrently. If you don't have a trace file of your own to test with you can use the following code to create a simple example.

--In one session run:
   declare @i integer
   create table test1 (column1 int, column2 varchar(10))
   select @i=0
   while @i < 10000
   begin
      insert into test1 values (@i,cast(@i as varchar(10)))
      select @i=@i+1
   end
   drop table test1
   go
--In second session run:
   declare @i integer
   declare @u integer
   select @i=0
   while @i < 10000
   begin
      select top 1 @u=column1 from test1 order by newid()
      select @i=@i+1
   end
   go

When using SQL Profiler to capture the trace there are certain events that need to be captured in order for the trace to be able to be replayed using this new feature. These events along with a few other requirements can be found here but I find it easiest to just use the predefined TSQL_Replay template as shown below.

Preprocess

Now that we have a trace file this file needs to be preprocessed by the controller. To do this we use the administration tool by specifying the preprocess option to the DReplay.exe executable. If you're not using a configuration file you can specify command line parameters as I've done in the example below, -i specifies the location of your trace file and -d is the output directory where the controller will put the intermediate files. The other parameter that is often used but not shown below is the -m parameter which tells the administration tool on which server the controller service is running. In my case I ran the administration tool on the same server as the controller windows service. You can find a complete description of all these options here and shown below is an example command line along with the output.

DReplay.exe preprocess -i C:\TSQL_Replay\TSQL_Replay.trc -d C:\TSQL_Replay
2013-10-17 16:04:07:145 Info DReplay    Preprocessing pass 1 of 2 in progress.
2013-10-17 16:04:07:676 Info DReplay    Preprocessing pass 1 of 2 completed.
2013-10-17 16:04:07:676 Info DReplay    Preprocessing pass 2 of 2 in progress.
2013-10-17 16:04:07:677 Info DReplay    Preprocessing pass 2 of 2 completed.
2013-10-17 16:04:07:692 Info DReplay    8 replayable events written to intermediate file 
in C:\TSQL_Replay.
2013-10-17 16:04:07:692 Info DReplay    Elapsed time: 0 day(s), 0 hour(s), 0 minute(s), 
0 second(s).

Once this process completes if we go and look in the output folder we specified above we should see the intermediate files. One xml file and one file with a .irf extension.

Replay

Now that the initial SQL Server trace file has successfully gone through the preprocess stage we are ready to run the replay. To execute the replay session we again use the administration tool this time specifying the replay option. As in the previous step you can override the configuration file by specifying command line parameters as shown in the example below. In this example the -d parameter tells the controller where the intermediate files are located, the -w parameter is a comma separated list of the clients that will execute the statements to be replayed, the -s parameter is the target server that the clients will execute the replay workload against and finally the -o parameter specifies that the replay activity results be saved in trace files on each client. A full description of these and other options can be found here and below is an example along with the output.

DReplay.exe replay -d C:\TSQL_Replay -w CLIENT1,CLIENT2 -s REPLAYTARGETSERVER -o
2013-10-18 10:25:58:453 Info DReplay    Dispatching in progress.
2013-10-18 10:25:58:457 Info DReplay    0 events have been dispatched.
2013-10-18 10:26:02:453 Info DReplay    Dispatching has completed.
2013-10-18 10:26:02:453 Info DReplay    8 events dispatched in total.
2013-10-18 10:26:02:454 Info DReplay    Elapsed time: 0 day(s), 0 hour(s), 0 min
ute(s), 3 second(s).
2013-10-18 10:26:02:454 Info DReplay    Event replay in progress.
2013-10-18 10:26:32:473 Info DReplay    CLIENT1: 3 events replayed, 
3 events succeeded (pass rate 100.00 %).
2013-10-18 10:26:32:473 Info DReplay    CLIENT2: 3 events replayed, 
2 events succeeded (pass rate 66.66 %).
2013-10-18 10:26:32:490 Info DReplay    6 events (75.00 %) have been replayed. 
Estimated time remaining: 10 second(s).
2013-10-18 10:26:33:640 Info DReplay    Event replay has completed.
2013-10-18 10:26:33:640 Info DReplay    8 events (100 %) have been replayed in total.
 Pass rate 87.50 %.
2013-10-18 10:26:33:640 Info DReplay    Elapsed time: 0 day(s), 0 hour(s), 
0 minute(s), 32 second(s).

One thing I want to point out with the above output is that you'll notice that one of the events on CLIENT2 did not succeed. This is because the second session from our example will fail when the first session drops the table.

Results

After the replay process has completed if we check on each client we should have a trace file in the results directory (as specified in the client server configuration file). In my case these files could be found in C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir on each client computer. Just to confirm the error, if you open the trace file on CLIENT2 you should see the following error:

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'test1'. (State 42S02) (Code 208)

Also in this trace file you can see the duration and rowcount statistics for each statement in your replay session and you can also confirm that each client was executing statements using different SIDs. A full description of these and the other events that are captured in the replay trace file can be found here.

Next Steps

• Try using this example to execute a really complex workload with many concurrent sessions
• Read my other tips on using SQL trace files to replay SQL Server workloads:
  • Using the replay feature within SQL Server Profiler
  • Configuring the SQL Server Distributed Replay feature

About the author

Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips