RML Utilities: An Internal MS Jewel for Performance Analysis

Mar 11, 2009 / By André Araújo

Tags: ,

Early in 2008 I came across a toolkit that Microsoft had released a couple of months before, called RML Utilities. I downloaded RML Utilities from the Microsoft website, started playing with it, and found it quite interesting.

Then I tried to find more information about it, blog posts, stories of people using it, only to realize that not much existed about it. Back then, Google only had a vague idea of it.

More than one year after that, and one cumulative update later, I still find that relatively few people in the SQL Server world know about this toolkit. When I ask a SQL Server DBA about RML Utilities it’s not uncommon to hear in reply: “RML-what?”

The toolkit

RML Utilities is a set of tools that Microsoft’s Customer Support engineers created to make their own life easier when troubleshooting performance issues for their SQL Server clients. These tools allow you to easily capture, modify, and replay workloads from a production environment against a test database.

After these tools matured internally, Microsoft decided to bundle them in a toolkit and release them publicly under the name of “RML Utilities”.

“RML” stands for Replay Markup Language, which is a XML syntax used to represent workloads. The RML Utilities toolkit contains applications to generate RML files from SQL trace files and to replay them single- or multi-threadedly, with options to coordinate the multiple threads timings to simulate the original load as closely as possible.

Even though I’ve used the toolkit many times since I first learned about it, I’ve never used it to replay a single workload, so I can’t tell you much about those features. What I like about the RML Utilities toolkit is in fact a by-product of it.

Performance analysis

When processing the trace files to generate the RML workloads, Microsoft realized that they could, at the same time, process all the performance information stored in those files and make it somehow available. The ReadTrace.exe application, which is responsible for processing the trace files, then added the ability to save all the performance analysis data into a database for later use.

To present the performance analysis they then created another tool, Reporter.exe, which connects to the previously loaded database and shows the results graphically. This particular ability to pre-process the performance data and show it in a user-friendly way was the feature of the toolkit that caught my eyes and the one I’ve used the most. The Reporter’s graphs provide a quick way to understand the resource consumption of the workload being analysed and give you a good idea of where the performance bottleneck might be.

ReadTrace also adds value when pre-processing the performance information. It does some smart things such as aggregating statistics for similar statements with different literals. Using SQL Profiler it is difficult to identify and quantify the performance impacts of such statements. In Reporter, because the statistics are aggregated, it’s very easy to see how many times the similar-but-different statements ran and how much resources they used.

Add it to your utility belt

I certainly don’t think the RML Utilities have come to replace any of the other tools a SQL Server DBA already has available to analyse performance. But I do find it a very good addition to my toolbox, giving me some insights that other tools were oblivious to.

RML Utilities, SQL Profiler, wait events, dynamic performance views, and Perfmon, amongst others, enable you to look at performance issues from different angles; they complement each other in the solution of the problem.

Get familiar with them and keep them at hand.

3 Responses to “RML Utilities: An Internal MS Jewel for Performance Analysis”

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>