How to Set Up a SQL Server Stress Test Environment in 8 Steps: Lessons From the Field

Step 4: Generate a Workload Script for One User

The next step is to collect all the information you will need to put in place a list of commands that represent the workload of one user, including think times.

SQLProfiler will help you trace a user's workload and determine what actions the application performs against your database but the replay feature does not tend to respect the think times. As we want to simulate real life experience, we will implement this manually.

• Ask a business user to perform a realistic work process as he would in real life situation, including realistic intervals between tasks.
• Trace this activity using SQLProfiler (only textdata and starttime are important)
• Replace variables with placeholders for parameters that can be defined at random. (This is where you wanted all database interactions to be performed using stored procedures.)
• Add think times to the script.

The next piece of code is an example how your workload file may look. This script will generate

• A random timestamp between 10 and 20 sec that will be used as think time.
• A random character.
• A random integer between 20 and 100.

Declare @RandomTime char(8), @RandomString char(1), @RandomInt int
SET @RandomTime = ''
-- sleep random period between 20 and 40 seconds
SELECT @RandomTime = convert(char(8), DATEADD ( ss , (rand() * 10) + 10, '20000101' ), 108)
SET @RandomString = char(rand()*26 + 65)
SET @RandomInt = rand() * 80 + 20
WAITFOR DELAY @RandomTime
print 'RandomTime= ' + convert(varchar(25), @RandomTime) +' RandomString= ' + @RandomString + ' RandomInt= ' + convert(varchar(10), @RandomInt)

This script will run as one virtual user. It can be stored in SQL Server as a stored procedure; in that case, don't forget to create the stored procedure with recompile, if not, your variables will not be regenerated between runs.

Step 5: Generate a Script that Will Launch "X" Virtual Users

Your next step is to run the stored procedure you created in step 5 for multiple virtual users. This is where your parameters play an important role, if not; all virtual users would access the same set of records causing unrealistic locking. I prefer also to use variable think time values because in reality, they will also vary between a minimum and maximum value.

Although I promised not to publish any C# code of my own in a previous article, I will take the risk this time. In .NET, there is a simple way to simulate a variable number of virtual users using threads. Although it is designed for asynchronous processes, you don't need to react on the termination event, which makes it even easier to use.

The following piece of code is part of your click event where one connection object is created for each virtual user you define in <yourNumberOfUsers>.

private void btnStart_Click(object sender, System.EventArgs e)
{
            int numberOfUsers = int.Parse(<yourNumberOfUsers>);
            for (int i=0; i<=numberOfUsers-1; i++)
            {
                        LoadConnection oLoadConnection = new LoadConnection();
                        Thread oThread = new Thread(new ThreadStart(oLoadConnection.startConnection));
                        oThread.Start();
                        Thread.Sleep(<yourIntervalBetweenUserConnections>);
            }
}

The following piece of code is part of your connection class. Don't forget to disable connection pooling!

public class LoadConnection
{
              public void startConnection()
              {
                          Try
                          {
                                      SqlConnection conn=new SqlConnection();
                                      conn.ConnectionString = "Integrated Security=true;Initial Catalog =<yourDatabase>;Data Source=<yourServer>;Connect Timeout=600;Pooling=false;Application Name='<yourApplicationName>'";
                                      conn.Open();
                                      SqlCommand comm=new SqlCommand();
                                      comm.Connection=conn;
                                      comm.CommandTimeout = 600;
                                      comm.CommandType = CommandType.StoredProcedure;
                                      comm.CommandText = "<yourStoredProcedure>";

                                      comm.ExecuteNonQuery();

                                      conn.Close();
                          }
                          catch(Exception ex)
                          {
                                      MessageBox.Show("Error",ex.Message);
                          }
              }
};