7

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?

6

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.

  • Good stuff Spaghetti, your comment about setting up a trace prior to replaying the initial capture is what I was looking for. I was looking at using this tool for capture and analysis. dbsophic.com/qure-analyzer. I suppose the same methodology applies however. – Tom May 14 '15 at 8:51
  • @Tom Qure Analyzer is a great tool. I used it a couple of times and it gets the job done. It's also more visually appealing than RML tools, which are quite old-looking. However Qure Analyzer does not support command line parameters and cannot be automated. If you have to do it regularly, it's a big plus for RML tools. – spaghettidba May 15 '15 at 7:18
0

Try out Microsoft Database Experimentation Assistant: https://www.microsoft.com/en-us/download/details.aspx?id=54090 It can provide performance information based on each of the queries.

  • 1
    I think you need to provide a bit more information in this answer - simple one-liners are not really the done thing here. A couple of references maybe - an example or two - details of the information provided by the tool you are recommending? p.s. welcome to the forum! :-) – Vérace Jun 7 at 4:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.