ThatJeffSmith

Replaying Activity in SQL Server

Load testing is an interesting topic. Application load testing is what most people are familiar with. They either use something like LoadRunner, or they ask half the company to ‘jump on the website and start clicking buttons.’

But what about the database? In this case, what about SQL Server? Will it be able to sustain the workload as it increases? Will the performance SLAs hold? Does the average response time for a transaction increase at a predictable rate as the user load goes up?

Quest sells a solution called Benchmark Factory for Databases. This tool allows you to generate load tests and deploy them to Oracle, SQL Server, DB2, Sybase, and MySQL. You can either run a synthetic test like the TPC-H or TPC-E, or you can actually replay activity that you have recorded in a trace.

Here is a quick example of replaying load for SQL Server 2008 R2.

Step 1: Profiler

  • Open the SQL Server Profiler.
  • Record activity to a table
  • Be sure to only record what is of interest for the purpose of your test.
  • Try a small sample first, don’t go crazy with a 24hr replay scenario just yet.
  • Step 2: Run Your Application
    As the application is running, all of the SQL and T-SQL calls will be recorded in the Profiler and stored in the table you created in step1. Even in a small amount of time, you will have a lot of transactions. For my example I ran in 5 minutes and captured over 90,000 calls.

    Step 3: Stop Your Trace/Profiler Session

    Step 4: Open Benchmark Factory for Databases

    Step 5: Launch the Load Scenario Wizard
    The Load Scenario Wizard will step you through the process of defining your replay test for SQL Server.

    You may see this error message, just click through it and proceed.

    Now enter your connection information.

    Benchmark Factory will allow you to browse the databases, and will recognize the profiler table you created.

    Benchmark Factory will now create a project based on the transactions recorded in your table. If you want to filter out the activity to be replayed you can either:

  • Configure the Profiler to ONLY capture what you want to replay
  • Use your magic SQL skills to trim the table before you create your project in Benchmark Factory.
  • Step 6 – Set Your Project Options
    Now you have your Benchmark Factory project and you are ready to replay the recorded load on your database. You have a few options to explore first.
    How many users do you want running the transactions?
    Let’s assume you recorded a single user running the application. Now you want to see what will happen if 100 users are running the same load concurrently. Just set the ‘Users’ value for your sceneario to ’100′. You can also optionally have the number of executions go up from the default of ’1′. This would allow you to run the test several times in one job.

    You can also manipulate the latency for the transactions. It will be default run the queries as captured and honor the timings. However, if you want to enforce an absolute interarrival time between calls, you may do so. You can artificially add keying and think times to simulate the users experience while they are running your application. Removing all of the latencies will cause the project to run as fast as your SQL Server can respond to the queries. It is critical that you define your test plan BEFORE you start jumping into the software to run your test.

    Step 7 – Run Your Test

  • Submit the job
  • Optionally add any counters you want to capture
  • View the Real Time Statistics as the test is running.
  • Step 8 – View the Results
    Benchmark Factory stores the results in a local repository. You can view key metrics such as maximum response time, bytes per second, transactions per second, and any optional counters you added in step 7.

    The graphs can be exported as image documents for your own custom reports. You can also export all of the data to an Excel spreadsheet.

    Tips

  • After several thousand transactions, Benchmark Factory will write them out to an XML file instead of listing them separately in the project. For the smaller project, you will be able to track performance at the query level instead of the transaction level. You will also be able to set latencies and bind variables at the query level. If you want to be able to do this, be sure to limit the amount of queries you are asking Benchmark Factory to replay.
  • Try to run your test as recorded without making any changes first. Then tweak it to fit your needs.
  • Script build up and tear down directly into your Benchmark Factory project. This way, when you execute the job, it will set your database environment to the proper state automatically.
  • Read the 5 Star Review of Benchmark Factory
  • Join the Benchmark Factory Community