The Microsoft SQL Server support team uses several internally written utilities to ease the work that is related to a typical customer support case. Database developers and system administrators can also use these utilities to work with Microsoft SQL Server 2000 or with Microsoft SQL Server 2005. These utilities are collectively called the Replay Markup Language (RML) Utilities for SQL Server.

Back to the top

You can use the RML Utilities for SQL Server to perform the following tasks:

•	You can determine the application, the database, the SQL Server login, or the query that is using the most resources.
•	You can determine whether the execution plan for a batch is changed when you capture the trace for the batch. Additionally, you can use the RML Utilities for SQL Server to determine how SQL Server performs each of these execution plans.
•	You can determine the queries that are running slower than before.

After you capture a trace for an instance of SQL Server, you can use the RML Utilities for SQL Server to replay the trace file against another instance of SQL Server. If you also capture the trace during the replay, you can use the RML Utilities for SQL Server to compare the new trace file to the original trace file. You can use this technique to test how SQL Server behaves after you apply changes. For example, you can use this technique to test how SQL Server behaves after you do the following:

•	You install a SQL Server service pack.
•	You install a SQL Server hotfix.
•	You update a stored procedure or a function.
•	You update an index or create an index.

This version of the RML Utilities for SQL Server supersedes the earlier version. The earlier version of the RML Utilities for SQL Server is described in Microsoft Knowledge Base article 887057. For more information, click the following article number to view the article in the Microsoft Knowledge Base: The earlier version of the RML Utilities for SQL Server supports the SQL Server 2000 trace file format only. This version of the RML Utilities for SQL Server supports the SQL Server 2005 trace file format and the SQL Server 2000 trace file format. If SQL Server 2005 is installed in your environment, you should always use this version of the RML Utilities for SQL Server even if you do not process SQL Server 2005 trace files. This version of the RML Utilities for SQL Server contains important software updates, new features, and performance improvements.

Back to the top

How to obtain the RML Utilities for SQL Server

The following files are available for download from the Microsoft Download Center:
Download the RMLSetup_X86.msi package now. (http://www.microsoft.com/downloads/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en)
Download the RMLSetup_AMD64.msi package now. (http://www.microsoft.com/downloads/details.aspx?familyid=B60CDFA3-732E-4347-9C06-2D1F1F84C342&displaylang=en)
Note Microsoft provides the RML Utilities for SQL Server as-is. Microsoft Customer Support Services (CSS) does not provide support or updates for the RML Utilities for SQL Server. If you have a suggestion or if you want to report a bug, use the e-mail address in the Help file (RML Help.xps).

Back to the top

Description of the RML files

The RML Utilities for SQL Server are a family of utilities that create RML files and that process RML files. An RML file consists of XML elements. For example, an RML file contains an XML element that describes a connection. The following is an example of the CONNECT XML element.

<CONNECT SEQ="1112151" NEXTSEQ="1112290" DELTA="17252000000">
            <CMD></CMD>
            <SERVER>SERVERNAME</SERVER>
            <NTUSER>TestUser</NTUSER>
            <NTDOMAIN>TestUser</NTDOMAIN>
            <HOST>MyComputer</HOST>
            <APPNAME>ApplicationName</APPNAME>
            <DBID>7</DBID>
</CONNECT>

For a complete description of RML and the meaning of each element and of each attribute, see the Help file after you install the RML Utilities for SQL Server.

Back to the top

Benefits of the RML Utilities for SQL Server

You can use the RML Utilities for SQL Server to manage the performance of SQL Server. You can use the RML Utilities for SQL Server to process SQL Server trace files. You can also use the RML Utilities for SQL Server to determine the applications and the queries that cause a particular bottleneck. The bottleneck may be the CPU or the disk I/O. The RML Utilities for SQL Server contain the Reporter utility. You can use this utility to focus on specific time frames for a specific application, database, or SQL Server login. You can capture the trace to generate RML files. Then, you can replay the RML files against an instance of SQL Server. This is useful if you want to simulate application testing when it is impractical or impossible to test by using the real application. For example, in the test environment, it may be difficult to generate the same user load that exists in the production environment. During the replay, you can capture another set of traces. Then, you can compare the performance characteristics of the replay trace files to the original trace files. This kind of regression analysis would otherwise be a difficult process that you would have to perform manually.

The Help file contains a "Quick Start" topic. This topic includes a brief exercise that will familiarize you with each RML utility. To open the Help file, click Start, point to All Programs, point to RML Utilities for SQL Server, and then click RML Help.xps.

Back to the top

Utilities in the RML Utilities for SQL Server

The ReadTrace utility

The ReadTrace utility is a command prompt utility that you can use to process trace files that are generated by SQL Server 2000 or by SQL Server 2005. The ReadTrace utility creates RML files that can be processed by the OSTRESS utility to replay the actions in the trace. You can also use the ReadTrace utility to populate a database by using the normalized trace data. You can use the normalized trace data for performance analysis of the SQL Server 2005 workload. Notice that the performance analysis feature uses only a database in SQL Server 2005 and in later versions.

The performance analysis feature of the ReadTrace utility normalizes trace data and then bulk loads the normalized trace data into a database. Then, you can use the normalized trace data in trend analysis and in performance analysis. The format of the normalized data typically reduces the data size anywhere from one-tenth to one-twentieth of the size of the original trace files. Even if you capture the SQL:BatchStarting event and the SQL:BatchCompleted event in the trace data, the ReadTrace utility only saves one row for a specific batch. Additionally, the ReadTrace utility normalizes the text of each event to determine the unique patterns. And, each event contains a reference to the query pattern instead of to the text of the event. For example, you have two queries that select data from a table. However, these queries use different literals. In this case, the ReadTrace utility normalizes these queries to the same form. You can use this normalized form to aggregate the data to learn cumulative information or to average information for all the occurrences of a specific query that is submitted by a client application.

Note A literal is an actual number, string, or value. For example, consider the following queries.

SELECT * FROM table1 WHERE name='Tom'
SELECT * FROM table1 WHERE name='Mary'

In these queries, Tom and Mary are literals. If you ignore these literals, these queries represent the same query.

The Reporter utility

The Reporter utility is a Microsoft .NET Framework-based application that you can use to view reports that contain performance information. The performance information is saved by the ReadTrace utility in the database. The ReadTrace utility automatically starts the Reporter utility when the ReadTrace utility finishes processing a set of trace files. You can manually start the Reporter utility so that you can view reports that are based on a database that was loaded previously.

To manually start the Reporter utility, click Start, point to All Programs, point to RML Utilities for SQL Server, and then click Reporter.

The OSTRESS utility

The OSTRESS utility is a command-line query utility. The OSTRESS utility is multithreaded and uses Open Database Connectivity (ODBC). You can use the OSTRESS utility to read input from a command-line parameter or from files that you specify in the command line. The OSTRESS utility supports the following input files:

•	SQL script files (.sql) that have standard go-delimited batches. Note Go-delimited batches are the batches that are delimited by the GO statement.
•	RML files (.rml) that are generated by the ReadTrace utility.

You can use the OSTRESS utility to process the input files in stress mode. You can also use the OSTRESS utility to process input files in replay mode. However, you can do this for RML files only. In stress mode, the OSTRESS utility creates one thread for each input file. All the threads run as fast as possible without synchronization among the threads. You can use stress mode to generate a specific type of stress load on the server. In replay mode, you can synchronize events by making sure that they replay in the sequence that these events originally occurred. Because the OSTRESS utility is a command prompt utility, you can use it as part of an automated process or as a test script.

Back to the top