HammerDB 4.5 for SQL Server TPROC-C exhibits much lower performance than HammerDB 4.2

[joshuajin]

I noticed a significant difference in performance between HammerDB 4.2 and 4.5 in my tests. Here are some test results. The numbers are in SQL Server TPM captured from the HammerDB GUI.

Environment: 128 vCPU, 512GB Ram, 13,000 warehouses, 256 virtual users, UseAllWarehouses, SQL Server DB.

Although the release note (see below) mentions that SQL Server TPROC-C has been refactored, I wonder if this performance decline is expected or if something needs to be looked at. Could the HammerDB v4.5 changes in the index property (allowing page lock) lead to the performance regression?

From the release notes: “...From version 4.5 the stored procedures for the SQL Server TPROC-C have been refactored and MySQL, MariaDB and Db2 modified. This increases the expected performance between versions and consequently the performance from HammerDB v4.5 & HammerDB v4.0 cannot be compared directly to the performance of v3.3 or previous releases dependent on the database being tested.”

Furthermore, performance degradation has also occurred for various virtual users (e.g., 256) settings.

Joshua J

[sm-shaw]

Firstly, yes, it is always good practice to only compare results when testing with the same configuration inlcuding the HammerDB version.

However, in this particular case this is a very common error in approach by overconfiguring your test environment and expecting higher core counts, warehouses and virtual users to automatically deliver higher performance, this is not guaranteed to be the case.

If we take an example of the v4.5 stored procedures (from v4.7) running on a 10-yr old i7-4770K test PC then we can see that it is outperforming your 128vCPU configuration running just 4 virtual users on 20 warehouses.

As contention increases, this can and will lower performance.

So you need to take a step back and instead build a performance profile as described here https://www.hammerdb.com/blog/uncategorized/hammerdb-using-mysql-5-7-vs-8-0-to-understand-performance-profiles/. Start small with 1 virtual user and a smaller warehouse count and build from there. There are so many interdependent factors that can impact performance, once you build this profile you can see where on the curve performance is limited and determine the factors that are causing contention.

[joshuajin]

This is a valuable information that we can't simply over configuring the hardware in the test environment and hope for higher TPM throughput. For my initial question, I may come back with you after I build the performance profiles for 4.2 and 4.5. Thank Steve!