Time to time I have the need to simulate the workload for different reasons, such as exercise the SQL Server P/T, knowing how much load the SQL Server can take or even want to replicate some condition cause my sql server crash. I like to use this post document some of the method I have found.
- DTM DB Stress
- Quest Benchmark Factory
- Embarcadero DB db-optimizer
Hammerora is the open source database load testing application. It can run the TPC-C and TPC-H load against MSSQL, Oracle and other DBMS. The latest version when this post is written is 2.9. However, you need to go to the sourceforge page and browser the download to find the 2.9.
Setup the Benchmark type
Select the option –> Benchmark and choice the DBMS and the Benchmark options.
In this case, I selected MSSQL Server and TPC-C.
Configure Server connection and warehouse
options –> TPC-C Schema –> Build and Driver
This screen is for setting up the database connection and base configuration.
Warehouses value is to decide the size of the testing database. Warehouse 1 is around 130MB. In this case, I use 5 which would build the database name tpcc with 650 MB of dummy data.
There are 2 type of TPC drivers script we can choice.
Standard driver script: Hammerora would run the continuously testing until you stop it.
Timed Test Driver Script: You can set up how long you want to run the test load.
There is also the option to configure how many virtual users would used.
After all configure is done. click cube button to create the schema.
This would take sometime to completed….
Test the virtual user script
Create the 10 Virtual Users testing
Run Virtual user loads
Use activity monitor to monitor the server status
In theory, you can write any application to run the customize query to simulate the load. Perl is good as any. My college write the shell script and connect to MSSQL from unix and run the query. In the Shell script he put the for loop and spawn numerous of background processes which would do the job.
Since I am primary focus on testing the MSSQL load. It make sense to explore the powershell option. It turns out it is not easy as using Shell. As far as my research goes, there are 2 ways to simulate the threading in powershell. Background jobs and runspace.
You can find the runspace example from here.
In this test, I just execute the simple sp: uspGetEmployeeManager from AdventureWorks.
Set up the Iterations to 50 with 30 threads.
Click the database for configure the connection.
The good thing for this tool is parameter matching. You can substitute with store procedure’s parameter from select statement. In this example, I substitute employee ID from Employee table.
Click the Go to run the test
It seems to be easy to use from the screenshot, however, I can not make it connect to my SQL Server 2008R2 instance. I have tried both default instance and name instance but can not get it work.
Here are some other tools I found in the internet but not yet test.