This wiki is a transcript of a previously recorded video.

Related content assets:

SQL Server 2012 Distributed Replay

Hey folks – this is Joe Sack, I’m a Principal Consultant with SQLskills, and this is the demonstration that accompanies the presentation on Distributed Replay for SQL Server 2012. And this demonstration is really a tour, a brief tour of the different components – recall if you will there’s the admin connection – so I’m a DBA and I’m connecting to the Controller, and then the Controller dispatches commands to the various clients that drive load against the target SQL Server instance.

So I’m going to just hop out of here and what I did is I actually put some of the local files under a DRUDemo and a few of these batch files just simplify the process of this demonstration – again this is more of a tour of what are different components.

And if you look here I have a trace file, DenaliDRUDemo, that’s a trace file that I captured earlier against a SQL Server instance that I want to replay against a target SQL Server instance. And if I open up a pre-processed file – so I had that original trace file and you can’t just load that file – that file isn’t natively used by Distributed Replay against the target server – it needs to pre-process it so it has intermediate files that are then used by the Controller and dispatched to the clients.

So in this case dreplay preprocess and the directory of the trace file and then just the directory of where I’m putting those intermediate files. So let’s go ahead and open up command line and I will execute that.

Alright and that was pretty quick and so we’ll go to DRUReplay and sure enough there are two files right there that are ready. Now, a third aspect is replay trace – I have an actual dreplay command where I’m saying okay, dreplay replay, and then the name of the target SQL Server instance – in this case it’s a self-contained environment. And then also the client, the Distributed Replay client. Now in your actual topology that you deploy you’ll want to use multiple clients. You can choose to still use one, but one of the benefits is that you can have a comma-separated list of the different clients that have the installed Controller, oh I’m sorry, the installed clients that all point to the same Controller. So you might install the Controller and multiple clients and then they drive the load to the target server instance.

So before I kick that off, I’m also going to set up a trace, I’m going to start a trace that looks at the target SQL Server instance so I can just show you that indeed activity is flowing. Let’s just execute that. Alright, so there are 200-som events that it’s going to try to execute – we’re not going to wait for the whole thing but right now it’s set to run in the original amount of time that it was traced in – so it’s trying to emulate that. So right now it’s replayed about 41 events and if I look here then sure enough there’s activity flowing through.

Now what if wanted to increase the flow of that? So really I want to double the performance, I want to drive more load to simulate, for example, adding new users to an application. Let me go ahead and CTRL-C on this option, and yes I want to cancel it – terminate job. What I’m going to do is I’m going to go to a configuration file, there are a few different configuration files and setting but this one’s a very key one. So DReplay.exe.replay.config and if we look here there are two of the options we talked about in the presentation today – so ConnectTimeScale 100, which is the original amount – and ThinkTimeScale. What if we said, look okay, ThinkTimeScale, let’s decrease that down to just 1% of that so we want 99% faster. Alright I’m going to go ahead and save that.

Now before you noticed it was slowly going through the different results, based on the original cadence of the captured trace and going to go ahead and stop this and restart the original trace so you can watch how quickly this flows through. And I’m going to replay that trace except keep in mind that configuration file has now changed.

So looking at this, it says dispatching in progress, event relay in progress, let’s take a look at how quickly this happens. Now I didn’t change the connect login time, I could have also changed that as well, but what you’ll notice behind the scenes is that a bunch of events just flowed through and the command line window says 240 events have been replayed.

So that was an example of pushing that load much faster.

So this was a demonstration, or a quick tour, but keep in mind if you had a much larger workload and you want to drive it twice as fast, this would be one of the ways you could do it in stress mode.

So that was the demonstration – I hope you also check out the presentation that goes along with this and thank you for watching!

Return to SQL Server 2012 Developer Training Kit BOM (en-US)