HammerDB Load Testing

[nhariba2]

I have installed HammerDB on windows PC 8GB Ram/i5 Dualcore.
Trying to performance benchmarking comparison between on-prem sql server Vs SQL Server on Azure Arc (on-prem)
Any test we perform , CPU on my PC (where HameerDB is installed) is shooting to 100%.
So unable to collect the metrics we need.
Note: Connection to target system is getting established, databases\transactions are getting generated on the target servers, but not able to collect those metrics on my PC (where HammerDB is installed)

Any advise ?

[sm-shaw]

Hi, the issue is not entirely clear but it sounds like you are testing a database on your local machine instead? The best place to start is with the quick start guide https://www.hammerdb.com/docs/ch02.html so the steps are, 1. build a schema, 2, run a test workload to make sure it is working, 3. run a timed workload, 4 check the transaction counter. Make sure the connection information is for the remote system and not the local one. The workload runs with stored procedures so all of the load in on the database.

[nhariba2]

@sm-shaw I have installed HammerDB on windows PC 8GB Ram/i5 Dualcore.
Trying to performance benchmarking comparison between on-prem sql server Vs SQL Server on Azure Arc (on-prem)
Any test we perform , CPU on my PC (where HameerDB is installed) is shooting to 100%.
So unable to collect the metrics we need.
Note: Connection to target system is getting established, databases\transactions are getting generated on the target servers, but not able to collect those metrics on my PC (where HammerDB is installed)

Any advise ?

[sm-shaw]

Great, so how many Virtual Users have you created, does it still do it with 1 VU? Also make sure that you have set the option "Exit on SQL Server Error" - if sessions are spinning without running transactions or having them error then this will cause the CPU to spike on the client. Also check task manager/resource monitor to see where the CPU is going and confirm it is in HammerDB. Have you run the test workload with output with a small number of virtual users to make sure it is running correctly. Also check the network configuration ODBC in particular can use more bandwidth.

[nhariba2]

@sm-shaw Am trying to build the schema in hammerdb but its taking time to creating rows on tables its keeps on generating nearly 1lakhs records. May i know how to reduce number of records creation, due to this issue am unable to proceed on hammerdb. Its taking more than 5hrs also still records keep on generating. May i know the values of virtual users and warehouse, etc to be entered? Those values should be complete schema build process quickly, here am help less. I have given varies values virtual user and warehouse still same issues taking more time to complete schema build. I have entered 1wareshouse and one virtual user for 8gb ram and 2core sql machine keeps on generating the rows on tables... Also let me know how many records will be generate on default while build schema on each tables?

[sm-shaw]

On a test PC it took around 1 minute to generate a 1 Warehouse schema on SQL Server, the largest table is order_line with 300342 rows. If it is taking a long time it sounds like an I/O issue. Using SSMS and check Data File I/O looking in particular at the Response Time - this should be in the low milliseconds.

[nhariba2]

There is no I/O issue, i have checked the I/O output in SSMS showing 1ms. I have used one virtual user and one warehouse for azure arc sql virtual machine this running three hours to create records for customer table nearing 70k rows now. still schema build is not yet completed - Please help us to override this problem

[sm-shaw]

OK, so instead are you trying to load a system in the cloud from a local client PC? If so how far away is the azure system from you and what is the network roundtrip time? By default HammerDB will create the schema and create and load the data for you without intermediate files. However it does this by creating the data and using an insert operation for a small batch of rows at a time. If the network roundtrip is a long time then this will clearly take a lot of time. Your options then are:

1. Also put the client in the cloud so it is near the database server
2. Use the Datagen Feature (the one with the cloud upload symbol!) - this was designed for exactly this purpose. You create flatfiles locally, upload them to the cloud, create your schema and then use BULK INSERT to load the data. This will be much quicker then loading by insert over a long distance network. However there is more admin as you have to create the schema and load it yourself.
   In either case once you have done the load also consider the impact of the roundtrip time on the test as well.

[sm-shaw]

@nhariba2 if the issue is network roundtrip time and the suggested actions solve the issue, please confirm that this is the case.

[sm-shaw]

No further update suggesting that the roundtrip time between client and server in the cloud was the issue. This can be resolved by also putting the client in the cloud or using the datagen feature.