Open SQL Server trace files - RML Utilities

In SQL Server, trace files are generated by SQL Server Profiler.

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly. 

Recently we ran with some issues with a web application which uses SQL Server as database. In order to try to analyze what was happening we received a TRC or Trace file, which was generated during a test.

My first try to open this kind of files was ClearTrace, but after a while I was not able to get much insight about the file. The program works fine and you can check some bulk information, but I needed a more fine grain.

Finally I found RML Utilities for SQL Server (RML stands for Replay Markup Language):

The RML utilities allow you to process SQL Server trace files and view reports showing how SQL Server is performing. For example, you can quickly see:

• Which application, database or login is using the most resources, and which queries are responsible for that
• Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed
• What queries are running slower in today's data compared to a previous set of data

When you install RML Utilities (from here), you install several tools (extracted from RML Help):

ReadTrace

ReadTrace consumes as input trace (.TRC) or (.XEL)files and .CAB or .ZIP files which contain .TRC files. The outputs from ReadTrace can be .TRC files, .RML files and the Performance Analysis (PerfAnalysis) database. (A populated performance analysis database is required for Reporter functionality.)

Reporter

Reporter is a .NET based application used for visualizing the trace data loaded by ReadTrace. Reporter displays Reporting Services based report (.rdlc) files by using the client Report Viewer control. A Reporting Services installation is not required. . The report-based infrastructure allows for interactive analysis that was not possible using the static .HTM output from prior versions.
Reporter is compiled for native use on ANY platform. When you review large data sets it is helpful to use larger 64 bit computers.

Ostress

OStress is a scalable, ODBC based application which can stress or replay database commands. In stress mode you can specify a query via a command line parameter, .SQL script or .RML file. Replay mode uses .RML files generated by ReadTrace as it processes SQL Server trace files.

ORCA

OStress Replay Control Agent (ORCA.exe) is a singleton COM object that provides session tracking, sequencing, delta timing and DTC transactional control for OStress replay. 

OStress handles the ORCA object creation and configuration unless you are performing a multiple instance replay.

In this case, to open trace files, you only need ReadTrace and Reporter. ReadTrace parses the TRC file and "moves" it into a SQL Server Database called PerfAnalysis (it's can be configured). Then, Reporter opens this Database, interprets this information, and shows it in a user friendly way.

So, starting with the TRC file, you have to execute de following command:

ReadTrace -ImySQLTrace.trc -omyOutputDir

I just needed specify the input file and the output dir because in my PC there is already a SQL Server installed. ReadTrace will use windows auntenthicantion to connect to it and it will create the PerfAnalysis database.

Once executed, Reporter will open automatically. If dont, you can start it manually.

I leave the funny part, interpret the data, for future posts :)