Tuesday, November 20, 2012

31 SQL Server Load testing: Hammerora,SQLQueryStress and others

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.

Hammerora

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.

image

Configure Server connection and warehouse

options –> TPC-C Schema –> Build and Driver

image

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.

image

There is also the option to configure how many virtual users would used.

image

After all configure is done. click cube button to create the schema.

image

This would take sometime to completed….

image

Test the virtual user script

image

image

Test complete

image

Create the 10 Virtual Users testing

image

Run Virtual user loads

image

image

Use activity monitor to monitor the server status

image

image

Shell

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.

PowerShell

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.

SQLQueryStress

Document: http://www.datamanipulation.net/SQLQueryStress/documentation/documentation.asp

In this test, I just execute the simple sp: uspGetEmployeeManager  from AdventureWorks.

Set up the Iterations to 50 with 30 threads.

image

Click the database for configure the connection.

image

Parameter Matching

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.

image

Click the Go to run the test

image

SQLStress

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.

image

 

Other tools:

Here are some other tools I found in the internet but not yet test.

31 comments:

  1. Thanks for sharing such informative article on Loadrunner Automation testing tool. This load testing tool will provide most precise information about the quality of software. Loadrunner training

    ReplyDelete
    Replies
    1. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a Java developer learn from Java Training in Chennai. or learn thru Java Online Training in India . Nowadays Java has tons of job opportunities on various vertical industry.


      or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

      Delete
  2. I am reading ur post from the beginning, it was so interesting to read & i feel thanks to you for posting such a good blog, keep updates regularly.Best Hadoop Training Institute In Chennai

    ReplyDelete
  3. I am following your blog from the beginning, it was so distinct & I had a chance to collect conglomeration of information that helps me a lot to improvise myself. I hope this will help many readers who are in need of this vital piece of information. Thanks for sharing & keep your blog updated.
    Regards,
    CCNA Training in Chennai | CCNA Training institute in Chennai | CCNA Training

    ReplyDelete
  4. Fantastic write-up together with excellent website, I enjoy these kinds of challenging content articles keep this specific somebody affordable seo packages

    ReplyDelete
  5. The future of software testing is on positive note. It offers huge career prospects for talented professionals to be skilled software testers. software testing course in Chennai | Software Testing Training in Chennai | Software testing course in Chennai

    ReplyDelete
  6. Greens Technology's. the leading software Training & placement centre Chennai & (Adyar)
    hadoop training in chennai

    ReplyDelete
  7. Greens Technology's. the leading software Training & placement centre Chennai & (Adyar)
    oracle-apps-hrms training in chennai

    ReplyDelete
  8. Wonderful post.This is really one of the most beneficial blogs I’ve ever browsed on this subject. I am very glad to read such a great blog
    Regards,
    Best software Testing Training institute in Chennai | Software Testing courses in Chennai | Testing Training Chennai

    ReplyDelete
  9. your information sybase very nice .it's more informative and gather new ideas implemnted thanks for sharing this blog sybase training In Chennai if want to get details now Greens Technologies Training In Chennai

    ReplyDelete
  10. Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    iOS Training in Chennai

    ReplyDelete

  11. Being new to the blogging world I feel like there is still so much to learn. Your tips helped to clarify a few things for me as well as giving..
    Android App Development Company

    ReplyDelete
  12. great and nice blog thanks sharing..I just want to say that all the information you have given here is awesome...Thank you very much for this one.
    web design Company
    web development Company
    web design Company in chennai
    web development Company in chennai
    web design Company in India
    web development Company in India

    ReplyDelete
  13. Being new to the blogging world I feel like there is still so much to learn. Your tips helped to clarify a few things for me as well

    iOS App Development Company
    iOS App Development Company

    ReplyDelete
  14. These ways are very simple and very much useful, as a beginner level these helped me a lot thanks fore sharing these kinds of useful and knowledgeable information.
    Fitness SMS
    Salon SMS
    Investor Relation SMS

    ReplyDelete
  15. it is really amazing...thanks for sharing....provide more useful information...
    Mobile app development company

    ReplyDelete
  16. This article is very much helpful and i hope this will be an useful information for the needed one. Keep on updating these kinds of informative things...
    Texting API
    Text message marketing
    Digital Mobile Marketing
    Sms API
    Sms marketing

    ReplyDelete
  17. Your very own commitment to getting the message throughout came to be rather powerful and have consistently enabled employees just like me to arrive at their desired goals."Devops Training in Chennai"

    ReplyDelete
  18. I have to voice my passion for your kindness giving support to those
    people that should have guidance on this important matter.



    Selenium Training in Chennai

    ReplyDelete
  19. Hats off to your presence of mind..I really enjoyed reading your blog. I really appreciate your information which you shared

    Java Training in Bangalore|

    ReplyDelete
  20. It’s great to come across a blog every once in a while that isn’t the same out of date rehashed material. Fantastic read.
    I’ve bookmarked your site, and I’m adding your RSS feeds to my Google account.
    dot net training in bangalore

    ReplyDelete
  21. Those guidelines additionally worked to become a good way to recognize that other people online have the identical fervor like mine to grasp great deal more around this condition.

    Hadoop training in bangalore

    ReplyDelete
  22. I am commenting to let you know what a terrific experience I enjoyed reading through your web page. I noticed a wide variety of pieces, with the inclusion of what it is like to have an awesome helping style to have the rest without hassle grasp some grueling matters.
    Hadoop Training in Bangalore

    ReplyDelete
  23. Hey, would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would enjoy your content. Please let me know. Thank you. AWS Training in Bangalore

    ReplyDelete
  24. It’s great to come across a blog every once in a while that isn’t the same out of date rehashed material. Fantastic read."Oracle Training in Bangalore"

    ReplyDelete
  25. I simply wanted to thank you so much again. I am not sure the things that I might have gone through without the type of hints revealed by you regarding that situation.
    Besant technologies Marathahalli

    ReplyDelete
  26. I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.
    rprogramming training in bangalore

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates