SQL11UPD02-TSCRIPT-09
This wiki is a transcript of a previously recorded video.
Related content assets:
Hey folks, this is Joe Sack here – I’m a Principal Consultant with SQLskills – and for this module I’m going to be covering Distributed Replay for SQL Server 2012
And specifically there are improvements around benchmarking capabilities so you can use Distributed Replay for benchmarking or app-compat considerations and we’ll talk about the different modes that you can run Distributed Replay in. But the key components, we’ll talk about that as well, so Distributed Replay Controller and then also the multiple clients that can be used as part of your Distributed Replay Process.
So to give you some context – 2008 – if you wanted to use native options, so let’s say you didn’t have a third-party choice for replaying a specific workload against a target SQL Server instance, you could use SQL Server Profiler – you could collect a replay trace and then replay it against a specific SQL Server instance. And that was ok except you were always limited to one client, so you would have one client running a load against a target SQL Server instance, and then the second aspect is that you didn’t have a lot of configuration options – so let’s say you wanted to triple the speed of a specific replay or if you wanted to change the think time, or change the connect time, you didn’t have an option.
So SQL Server 2012 Distributed Replay Utility is the set of functionality – you have a Distributed Replay controller and one or more clients, up to sixteen, that can be used to drive load against a target SQL Server instance. So you also have the options of configuring synchronization mode – so trying to replay at the original cadence – or stress mode – so trying to drive load as fast as you can, or maybe drive an incremental amount of load if you’re trying to predict application increase. So let’s say I’m adding 200 extra users, how would you go about doing that? You could adjust specific XML file… configuration file.
So in terms of the components – a few to be aware of – first of all the DBA workstation would have a command line to talk to the Controller and the Controller, as you would suspect from the name, is the mind behind the operations. So the Controller would coordinate across one or more clients, so you can still use one client if you want to but if you want to replicate a target server getting hit by multiple clients, so multiple connections, and maybe from different paths – this is your options. So you can go up to 16 different clients. And still one target SQL Server instance.
In terms of the process, the process, let’s say I want to do a specific replay test or a specific synchronization test against a target SQL Server instance, the first thing I’d want to do is I’d want to capture a trace file, like you did before in 2008/2008 R2, capture that trace file and then make sure that if its SELECT activity, I have less considerations, but if it’s INSERTs/UPDATEs/DELETEs going on as part of my file, I’m also making sure that I backup my database at the beginning of that process and then restore on the target server so that when I restart my replay, those are synchronized. So key thing is you backup and then duration of that replay – you’re doing the replay trace template.
And the second step is you take that trace file and then you feed it through dreplay command line to pre-process it, and that creates a couple of files that are then used by the Controller to dispatch to the one or more clients that are involved in the topology. And then those clients then drive load and the Controller synchronizes that activity across the load.
So in terms of modes, your choices are synchronization mode and the basic way you can think about that is its trying to replay based on the original activity. So if you want to go okay, I want to make sure I do no harm in this configuration on a target SQL Server instance, I want to replay this workload and I want to make sure it didn’t degrade – that could be a mode you could run in. Application compatibility is an appropriate use case for this specific synchronization mode.
And then stress mode is the flip side. Let’s say you want to drive as much as you can – so you want to stress test – so how much can we scale. Or capacity planning, so do we have enough in terms of resources – or even forecasting, so a common scenario I hear about is okay, we’re going to be adding 1000 more users, we anticipate this much more load – you could take that same profile or workload and then drive it up by let’s say 20% and you could say yep, we can withstand it, or nope, at 20% we’re seeing we’re seeing XYZ kind of bottleneck.
And a couple of things… there are a couple of options; we’re to go over that in more detail on the next slide.
So there’s Connect Time, and this one’s key because it’s the amount of time that the trace should wait between the trace starting and the logging of events starting – you can close that gap or keep the original gap.
There’s Think Time and this one’s I think very important. This is between the batches, so it’s the amount of time between the completion of the previous statement – let’s say a SELECT from a table – and then the next SELECT.
And then MaxIdleTime is a configuration – let’s say you were running a trace overnight and there was a one hour gap over that period, and you don’t want to wait one hour while you’re doing that replay, you can close that gap using this configuration.
A couple more options – so HealthmonInteral will allow you to configure how often a process wakes up to detect deadlocks. And then QueryTimeout is basically if there is an actual query that’s waiting, you can figure out the number of seconds that query should wait before it gets killed – so before that process is terminated.
So that was a brief overview of Distributed Replay utility and just to summarize, we have a better option now for benchmarking and testing. And it allows you to have multiple clients instead of just one client driving load and then the second area is you have more configuration options. So you can set synchronization mode or stress mode and then within that stress mode you can set different things like the Connect Time and Think Time.
Return to SQL Server 2012 Developer Training Kit BOM (en-US)
Richard Mueller edited Original. Comment: Removed (en-US) from title, added tag