We have a new server which we are migrating too and want to see the potential benefits of the hardware. So the idea is that we capture a workload and replay this against our new box.
My question is however in regards to seeing the performance benefits. I will be running some perf counters but ideally we want to see how much quicker individual queries are.
I will have this information in the initial workload capture, however when I replay this on my new server do I simultaneously need to run a trace to capture the new workload information also. In effect running SQL profiler twice at the same time for replaying and capturing?
Best Answer
Every time I had to do this, I used the RML Utilities (x86 and x64 package download links can be found on this page).
Basically, you just have to set up a server-side trace using one of the templates included in the package. The captured trace can be analyzed using ReadTrace (included in RML Utilities) and it populates a database with query analysis results. The results are shown using a tool called Reporter, which is just reporting services repackaged for RML Utilities.
ReadTrace also produces the RML intermediate files that you can use for replay against the new instance. The tool for replay is called Ostress.
Before attempting the replay (or even the capture), read carefully the manual in order to understand how to synchronize the capture with the backup you will need to ensure that the workload runs against the same data, so that you get consistent results.
Before performing the replay, set up a trace on the new instance using the same template used for the initial capture. When the replay is over, you can analyze the workload using ReadTrace and use the compare feature in Reporter. It will show you which queries improved or got worse in the two captures, by CPU, elapsed and I/O.
It's not trivial, but it can be done with some patience and a bit of trial/error. Good Luck!
PS: the new version of RML utilities can also analyze a workload captured using Extended Events. In my experience, the files are significantly bigger this way. The trace templates used by RML and Distributed Replay are different, so don't try to use them interchangeably. If I recall correctly, there is a way to feed the Distrivuted Replay with Extended Events files, but I might be wrong. In my opinion this is an option you should be investigating only if Ostress can't keep up with an highly concurrent workload.