Using the SQL Server Distributed Replay feature


By:   |   Updated: 2013-11-01   |   Comments (1)   |   Related: More > 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 @[email protected]+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 @[email protected]+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.

TSQL_Replay trace template

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


Last Updated: 2013-11-01


get scripts

next tip button



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

View all my tips
Related Resources





Comments For This Article




Wednesday, July 22, 2015 - 10:56:27 AM - Ray Back To Top
Thanks for the post, I have ben working through the SQL Server 2014 Upgrade Assistant which uses DRC as it's engine. I am stuck on an error, and not sure how to get past it. I have verified my DRC service users have full permissions, any ideas? The problem is it is not creating an aggregate trace result file... Any help would be appreciated.. Ray 7/22/2015 8:29:42 AM: Initializing... 7/22/2015 8:29:44 AM: Info DReplay Preprocessing pass 1 of 2 in progress. 7/22/2015 8:29:46 AM: Info DReplay Preprocessing pass 2 of 2 completed. 7/22/2015 8:29:46 AM: Info DReplay 5023 replayable events written to intermediate file in working directory. 7/22/2015 8:29:46 AM: Info DReplay Elapsed time: 0 day(s), 0 hour(s), 0 minute(s), 2 second(s). 7/22/2015 8:29:46 AM: Info DReplay The dispatching is in progress. 7/22/2015 8:29:52 AM: Info DReplay The dispatching has completed. 7/22/2015 8:29:52 AM: Info DReplay 5023 events dispatched in total. 7/22/2015 8:29:52 AM: Info DReplay Elapsed time: 0 day(s), 0 hour(s), 0 minute(s), 5 second(s). 7/22/2015 8:29:52 AM: Info DReplay Event replay in progress 7/22/2015 8:30:16 AM: Info DReplay SQL2014-RL, 381 events replayed, 89 events succeeded (pass rate 23.35 %). 7/22/2015 8:30:16 AM: 381 events (7.59 %) have been replayed. Estimated time remaining: 5 minute(s). 7/22/2015 8:30:47 AM: Info DReplay SQL2014-RL, 881 events replayed, 166 events succeeded (pass rate 18.84 %). 7/22/2015 8:30:47 AM: 881 events (17.54 %) have been replayed. Estimated time remaining: 4 minute(s). 7/22/2015 8:31:17 AM: Info DReplay SQL2014-RL, 1282 events replayed, 177 events succeeded (pass rate 13.8 %). 7/22/2015 8:31:17 AM: 1282 events (25.52 %) have been replayed. Estimated time remaining: 4 minute(s). 7/22/2015 8:31:47 AM: Info DReplay SQL2014-RL, 2023 events replayed, 264 events succeeded (pass rate 13.04 %). 7/22/2015 8:31:47 AM: 2023 events (40.27 %) have been replayed. Estimated time remaining: 2 minute(s). 7/22/2015 8:32:17 AM: Info DReplay SQL2014-RL, 2425 events replayed, 271 events succeeded (pass rate 11.17 %). 7/22/2015 8:32:17 AM: 2425 events (48.28 %) have been replayed. Estimated time remaining: 2 minute(s). 7/22/2015 8:32:47 AM: Info DReplay SQL2014-RL, 2918 events replayed, 303 events succeeded (pass rate 10.38 %). 7/22/2015 8:32:47 AM: 2918 events (58.09 %) have been replayed. Estimated time remaining: 2 minute(s). 7/22/2015 8:33:17 AM: Info DReplay SQL2014-RL, 3322 events replayed, 310 events succeeded (pass rate 9.33 %). 7/22/2015 8:33:17 AM: 3322 events (66.14 %) have been replayed. Estimated time remaining: 1 minute(s). 7/22/2015 8:33:47 AM: Info DReplay SQL2014-RL, 3818 events replayed, 331 events succeeded (pass rate 8.66 %). 7/22/2015 8:33:47 AM: 3818 events (76.01 %) have been replayed. Estimated time remaining: 1 minute(s). 7/22/2015 8:34:17 AM: Info DReplay SQL2014-RL, 4222 events replayed, 338 events succeeded (pass rate 8 %). 7/22/2015 8:34:17 AM: 4222 events (84.05 %) have been replayed. Estimated time remaining: 50 second(s). 7/22/2015 8:34:47 AM: Info DReplay SQL2014-RL, 4723 events replayed, 382 events succeeded (pass rate 8.08 %). 7/22/2015 8:34:47 AM: 4723 events (94.03 %) have been replayed. Estimated time remaining: 18 second(s). 7/22/2015 8:35:06 AM: Info DReplay The event replay has completed. 7/22/2015 8:35:06 AM: Info DReplay 5023 events (100 %) have been replayed in total. Pass rate 8.87 %. 7/22/2015 8:35:06 AM: Info DReplay Elapsed time: 0 day(s), 0 hour(s), 5 minute(s), 15 second(s). 7/22/2015 8:35:07 AM: Failed to aggregate result trace files. Please make sure that there is result trace file under \\SQL2014-RL\E$\UAFS_2014TraceResults.


download


Recommended Reading

How to Grant Permissions to run SQL Server Profiler for a non System Admin User

Scheduling a SQL Server Profiler Trace

Use SQL Server Profiler to trace database calls from third party applications

Find Long Running SQL Server Queries Using Profiler

Using the SQL Server Default Trace to Audit Events





get free sql tips
agree to terms


Learn more about SQL Server tools