Relational Database Systems

Replay Trace using OSTRESS utility – MS SQL Server

In previous article, we discussed about Profiler to Replay Trace but if you have used it then you must have encountered some difficulty in running trace and also found that few events Profiler's Replay Trace does not cover these are:

  • Replay RPC events as remote procedure calls
  • Replay attention
  • Replay DTC transactions

To overcome difficulty in running profiler's replay trace and limitation of this tool, Microsoft has provided OSTRESS utility. We will discuss about this utility in this article and how to use it, We will also be learning about Read80Trace as it is also required in order to convert trace files into RML files that will be processed by OSTRESS utility. You can read more about these utilities at http://support.microsoft.com/kb/887057

So, lets first download these utilities from http://www.microsoft.com/downloads/details.aspx?FamilyId=5691AB53-893A-4AAF-B4A6-9A8BB9669A8B&displaylang=en and install on target server where you want to replay trace files.

Now follow step by step instruction to Replay Trace:

  1. Capture Trace files on Production Server
  2. Convert Trace files into RML files using following DOS command

ReadTrace –l<Trace File Location>\*.trc –o<RML Files Location>

 

For example: If you have all trace files available on FileShare Location like \\NASFS01\TraceFiles\ and you want to generate RML files on \\DBServer01\RMLFiles\ then your command would be:

      ReadTrace –l\\NASFS01\TraceFiles\*.trc –o\\DBServer01\RMLFiles

 

  1. Now configure Trace to capture activity when you are replaying trace on new server so that you can analyze/compare performance/behavior of command executed on old server and new server

 

  1. Now RML Files are ready to be replayed on new server to see how new server is behaving on new/same hardware configuration and/or different version of SQL Server. So Lets run following command to start replaying trace files:

 OSTRESS -creplay.ini -mreplay -T88 –i<RML Files Location>\*.rml –o<Replay Result Location>

 So if you want your replay result should be stored at \\DBServer01\ReplayResult then command would be:

OSTRESS -creplay.ini -mreplay -T88 –i\\DBServer01\RMLFiles\*rml –o\\DBServer01\ReplayResult

 Above command will produce result if executed for SQL Server 2008 and 2005 but will not produce correct result if executed for SQL Server 2000. But it can be correct by using –T146. So if you are replaying trace against SQL Server 2000 please make sure you use –T146 in above command

 

  1. Since, you had setup trace to run during ostress utility is replaying trace so lets analyze result to compare performance/behavior of query run on old server and new server by running following command which store result in database and generate report:

 

ReadTrace –I<Trace File Location>*.trc –o<RML After Replay Result Location> –d<Database Name –f

You can provide any Database Name it will be created if it does not exists

You need to include –T146 if you are processing trace files produced for SQL Server 2000

Summary

Whenever you are planning to upgrade server it is always highly recommended to analyze what you are going to get on new server from performance point of view which can be achieved using Replay Trace feature in SQL Profiler but ReadTrace and OSTRESS utilities are there to help you to ensure as close analysis as possible with less manual work to determine whether new server will provide desired result or not.

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top