SQL Load Test vs. Read80Trace and OStress

  • Article

A reader asked about my post on SQL Load testing, and why someone would want to use that vs. Read80Trace and OStress.

OSTRESS does straight record/playback of a trace file, but does not allow you to do experiments on mixing up commands, nor does it allow you to parameterize your tests.

For example on experimenting with different user activities, if you have a trace for a single user performing a particular scenario, OSTRESS can play that back, but doesn't give you a way to readily combine that with other scenarios. If you do have a trace of many users performing many scenarios, OSTRESS will play them all back, but doesn't tease them apart to simulate users.

The SQL Load Test tool allows you to get the traces of several scenarios then create interesting user models from them. For example you could have a script for a user buying a product, a different script for a user searching, then vary the model of how many users are performing each activity.

There's an inherent danger here, which is an advantage of OSTRESS, in that you may create the wrong mix, and end up optimizing the wrong SQL statements. Since OSTRESS is always playing back traces, if you use traces from production you know they are "real" patterns.

Another advantage is the SQL load test tool enables you to modify the code the code after it is generated. This can be necessary if say the result of one SQL statement is used as a parameter to a second statement.

So rather than replaying the static value at runtime, the SQL load testing tool enables you to get the value that is returned and pass it to the subsequent request.

Along the same lines, SQL load tests allow you to parameterize and vary parameter values in the test. For example, if you have a query on a customer order table, you can easily vary which customer is queried on. Of course the caveat above for test mix applies here as well, you may parameterize the "wrong" values and end up chasing after the wrong perf problems. 

I have not done the perf measurements, but my guess is that an advantage of OSTRESS is that it will be more efficient at generating load.

Ed.