SQL Server Stress Testing your database engine using RML utilities

Hi Friends,

As a DBA, stress testing and performance benchmarking is one of the most important activities that you would perform. There are many techniques and choice of tools at your disposal. In many cases, I have used RML utilities.

If you look into the description of RML utilities provided by Microsoft at http://support.microsoft.com/kb/944837, you will observe that they talk about the following benefits:

•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.

The above benefits are fine and understandable if you read more and use RML utilities. However, one benefit gets ignored here: Stress Testing your SQL instance.

The question is: “How can I stress my SQL Server to projected levels with a current workload?” And here comes the OStress utility from the RML pack.

RML stands for Replay MarkUp Language and consists of four utilities:

•ReadTrace

•Reporter

•OStress

•OStress Replay Control Agent (ORCA)

I want to highlight the OStress utility. OStress is a simple and scalable command line application which can stress or replay database commands. You can specify a query via a command line parameter, .SQL script or .RML file. You have switches to simulated number of connections and iterations for a given workload.

For example:

The following command runs stress_01.sql simulating 200 connections and iterates 2000 times.

Ostress.exe –ic:\AmitStresTest\stress_01.sql –n200 –r2000 –oc:\ AmitStresTest \output

And your stress_01.sql can contain simple or complex code that causes CPU or memory pressure. With this, you can test the scalability of SQL Server for a given workload. You can record performance benchmark etc,

Just an example of what stress_01.sql can contain: cross join with the largest table in your database.

The set of tools offer a great deal of help here. Before you get surprises in your production environment with huge workloads and simultaneous access, stress test now and have your answers ready.

RML utilities have a 32 bit and 64 bit versions which can be downloaded from http://support.microsoft.com/kb/944837

performance benchmarking, Performance testing, SQL Server performance tuning