RML Utilities is a free utility created by Microsoft and used by the SQL Server support team.  It’s available for download and if you work with SQL Trace data at all I’d recommend giving it a look. I know, I know, Trace is deprecated in SQL Server 2012.  But not everyone is running 2012 so until then, Trace files will still exist.

I first heard about RML Utilities years ago from Andrew Kelly ( b | t ) and have used it to analyze trace data from customers, which is really just a fraction of what it can do.  Today I’ve been working with it to compare two trace files.  Yes, that’s right…compare two trace files.  Imagine you run a load test in your production environment, then run that same test in your development environment which might have different hardware or updated code.  You can capture PerfMon metrics to compare Memory, CPU and I/O…but what about the performance of individual queries?  You can get that from the DMVs, but it’s really nice if you can look at the information side by side.  And I don’t mean two instances of Profiler open, I mean really compare the data.  RML Utilities will do that for you!

But, before I could get RML Utilities to do that, I had to get it to process a trace file.  This was my issue today.  I had two VMs, one with 2008R2 and 2012 installed, one with only 2012 installed.  The readtrace.exe file is used to process trace files, and the commands require the trace file (input), an output directory, a SQL Server instance and a database.  I had everything set up, but my process would fail with this set of messages:

09/27/12 15:49:44.262 [0X00000AA4] Readtrace a SQL Server trace processing utility.
Version 9.01.0109 built for x64.
Copyright (c) Microsoft Corporation 1997-2008. All rights reserved.
…extra text removed…
09/27/12 15:49:44.263 [0X00000AA4] –IC:\PerfLogs\Trace\File_RML_2008R2.trc
09/27/12 15:49:44.263 [0X00000AA4] –oC:\SQLskills\RMLOutput
09/27/12 15:49:44.263 [0X00000AA4] –SWIN2008R2PS\SQL2012
09/27/12 15:49:44.263 [0X00000AA4] –dBaselineData
09/27/12 15:49:44.263 [0X00000AA4] Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0×00060101 and Defined: 0×00060101
09/27/12 15:49:44.263 [0X00000AA4] Attempting to cleanup existing RML files from previous execution
09/27/12 15:49:44.264 [0X00000AA4] Using extended RowsetFastload synchronization
09/27/12 15:49:44.264 [0X00000AA4] Establishing initial database connection:
09/27/12 15:49:44.264 [0X00000AA4] Server: WIN2008R2PS\SQL2012
09/27/12 15:49:44.264 [0X00000AA4] Database: BaselineData
09/27/12 15:49:44.265 [0X00000AA4] Authentication: Windows
09/27/12 15:49:44.266 [0X00000AA4] Unable to connect to the specified server.
09/27/12 15:49:44.266 [0X00000AA4] ERROR: Performance analysis failed to initialize.  See previous errors and correct the problem before retrying.
09/27/12 15:49:44.266 [0X00000AA4] *******************************************************************************
* ReadTrace encountered one or more ERRORS. An error condition typically      *
* stops processing early and the ReadTrace output may be unusable.            *
* Review the log file for details.                                            *
*******************************************************************************
09/27/12 15:49:44.266 [0X00000AA4] ***** ReadTrace exit code: –9

My first thought was that it was having an issue with SQL Server 2012.  The documentation does not list SQL Server 2012 as a supported version.  So I tried it on my local machine against a 2012 instance, and it worked.  I reviewed the error message: “Unable to connect to the specified server.”  This made no sense, so I verified that I could connect with sqlcmd. Then I tried it against my other VM, thinking maybe it was something with VMWare.  That worked.  Then I started comparing the output.  From the successful process, I had the following:

09/27/12 15:59:07.349 [0X00000CD4] Readtrace a SQL Server trace processing utility.
Version 9.01.0109 built for x64.
Copyright (c) Microsoft Corporation 1997-2008. All rights reserved.
…extra text removed…
09/27/12 15:59:07.350 [0X00000CD4] –IC:\PerfLogs\Trace\File_RML_2008R2.trc
09/27/12 15:59:07.350 [0X00000CD4] –oC:\SQLskills\RMLOutput
09/27/12 15:59:07.350 [0X00000CD4] –SWIN2008R2-1\SQL2012
09/27/12 15:59:07.350 [0X00000CD4] –dBaselineData
09/27/12 15:59:07.350 [0X00000CD4] Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0×00060101 and Defined: 0×00060101
09/27/12 15:59:07.350 [0X00000CD4] Attempting to cleanup existing RML files from previous execution
09/27/12 15:59:07.351 [0X00000CD4] Using extended RowsetFastload synchronization
09/27/12 15:59:07.351 [0X00000CD4] Establishing initial database connection:
09/27/12 15:59:07.351 [0X00000CD4] Server: WIN2008R2-1\SQL2012
09/27/12 15:59:07.351 [0X00000CD4] Database: BaselineData
09/27/12 15:59:07.351 [0X00000CD4] Authentication: Windows
09/27/12 15:59:07.390 [0X00000CD4] Using SQL Client version 10
09/27/12 15:59:07.390 [0X00000CD4] Creating or clearing the performance database
09/27/12 15:59:07.892 [0X00000CD4] Processing file: C:\PerfLogs\Trace\File_RML_2008R2.trc (SQL 2008)
09/27/12 15:59:07.892 [0X00000CD4] Validating core events exist
09/27/12 15:59:07.892 [0X00000CD4] Validating necessary events exist for analysis
…more extra text removed…

My issue was that RML Utilities uses version 10 of the Client.  On the VM I only had SQL 2012 installed, which is version 11.  Once I downloaded and installed the 2008R2 Client (from here, you just need sqlncli_amd64.msi for an x64 machine), everything ran fine.  Thus, even though SQL 2012 is not listed as a supported version for RML Utilities, it looks like it will run as long as you have version 10 of the Client installed.  And as for comparing those files?  That’s for another post!