Microsoft KB Archive/887057

= Description of the SQL Server Performance Analysis Utilities Read80Trace and OSTRESS =

Article ID: 887057

Article Last Modified on 3/15/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



Introduction
The Microsoft SQL Server support team uses several internally-written utilities to make it easier to work on a typical customer support case. These utilities may also be useful to database developers and system administrators who work with Microsoft SQL Server 2000.

This article describes two main utilities, the Read80Trace utility and the OSTRESS utility. These utilities are provided as part of the Microsoft SQL Server Support Escalation Services Utilities (SQLEEUtils). This article also provides a link to download these utilities.

Note The Read80Trace utility and the OSTRESS utility are provided as-is. Microsoft Product Support Services (PSS) provides no support or updates for these utilities.



Read80Trace utility
The Read80Trace utility is a command-line utility that you can use to process trace files that are generated by SQL Server 2000. The Read80Trace utility generates replay markup language (RML) files as output. The Read80Trace utility can also populate a database by using normalized data that can be useful for analyzing system performance.

Note For performance analysis and aggregation, the Read80Trace utility requires that the destination database be a SQL Server 2000 or a later version database.

RML is an XML file format that is used to describe the events that occur on a specific connection. For example, in an RML file, an XML node has elements that describe a connection. The following is an example of the  node: 

1 2

<![CDATA[-- network protocol: TCP/IP set quoted_identifier on       set implicit_transactions on        set cursor_close_on_commit off set ansi_warnings on       set ansi_padding on        set ansi_nulls on        set concat_null_yields_null on        set language us_english set dateformat mdy set datefirst 7 ]]>

TestUser MyHost Microsoft Query Analyzer</APPNAME> <NETWORK>TCP/IP</NETWORK> <DBID>8</DBID> <PACKETSIZE>4096</PACKETSIZE>

</CONNECT> Note For a complete list of the XML nodes and the elements in each node, see the &quot;(R)eplay (M)arkup (L)anguage&quot; section in the SQLEEUtils Books Online.

When you use the Read80Trace utility to generate RML files, one RML file is generated for each connection in the trace file sequence. The RML directives are used by the OSTRESS utility to replay the activity of a specific connection.

When you use the Read80Trace utility to load the PerfAnalysis SQL Server database for trending and analysis activity, the Read80Trace utility uses a bulk load interface that is named IRowsetFastLoad from SQLOLEDB to quickly load the performance analysis database. The normalized data format typically reduces the data size to one-tenth to one-twentieth of the size of the original trace files. Only one row is saved for a specific batch, even if both the SQL:BatchStarting event and the SQL:BatchCompleted event are captured. Additionally, the text of each event is normalized to determine unique patterns, and each event stores a reference to the query pattern instead of the text of each event. For example, when you have two queries that select from a table but the queries use different literal values, the Read80Trace utility normalizes to the same form and points back to the same unique query pattern. This normalized form is also useful in aggregating the data to learn cumulative information or average information for all occurrences of a specific query that is submitted by the client applications.

OSTRESS utility
The OSTRESS utility is a multithreaded ODBC-based query utility. The OSTRESS utility reads input from a command-line parameter. The command-line parameter can be an RML file that is produced by the Read80Trace utility or a standard go-delimited .SQL script file. In stress mode, one thread is created for each connection, and all threads run as fast as possible without synchronization among the threads. You can use this mode to generate a specific type of stress load on the server. The replay mode lets you synchronize events by making sure that they play back in the sequence that they originally occurred. The OSTRESS utility also plays back events with the same relative duration between events. The following features are provided by the OSTRESS utility replay and are not provided in the Profiler replay tool:
 * Replay RPC events as remote procedure calls
 * Replay attention
 * Replay DTC transactions

Additionally, the OSTRESS utility is command-line based, and you can run the OSTRESS utility as part of an automated process or a test script.

To download the Read80Trace utility and the OSTRESS utility, visit the following Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?FamilyId=5691AB53-893A-4AAF-B4A6-9A8BB9669A8B&displaylang=en

<div class="references_section">