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
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
Now enter your connection information.
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:
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
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.