Upgrade from SQL server2008 to a higher version. The pressure test results are higher than those of the higher version installed directly & Under the same configuration, the pressure test results of hammerdb3.2 are higher than hammerdb4.7（SQL server）

[kevinlewang]

Hi Team,

After my tests, I found that Under the same configuration, the pressure test results of hammerdb3.2 are higher than hammerdb4.7（SQL server）& Upgrade from SQL server2008 to a higher version. The pressure test results are higher than those of the higher version installed directly

Is there any difference between hammerdb 3.2 and hammerdb 4.7 pressure test models？

Why are the results of the 2008 upgrade so high？

SQL server2019 upgraded based on SQL server2008

SQL server2019 is installed directly

[sm-shaw]

Yes, so firstly when making comparisons you should use the same version of HammerDB. Although every effort is made to maintain consistency across versions, the workloads will evolve over time and experts in a particular database are very welcome to update the workloads and submit a pull request if they think it can be improved, for example with SQL Server there was this change at v4.5 #371, so you shouldn't compare the results directly between v3.2 and v4.7.

Secondly, you are making comparisons with multiple changes so both the HammerDB version and the SQL Server version, you should keep the HammerDB version consistent (preferably v4.7) and then use this for your SQL Server tests.

Finally, although you don't mention the system you are testing and the number of CPUs, the virtual user count at 512 looks high, especially compared to the NOPM value. The question you are asking is "Why are the results of the 2008 upgrade so high?" However, the single data point you have captured and additional data is not sufficient to answer this question, as the values could be higher at a lower virtual user count. Here is an example with v4.7 on a laptop with 16 virtual users (one per system CPU).

I would recommend capturing data with a lower VU count to identify the point of maximum performance and then also capturing the data performance metrics to determine the reason for the difference in performance (especially if the configuration is different, which is entirely possible if there has been an upgrade).

Also, the response time profile data has improved between v3.2 and v4.7.

[kevinlewang]

Hi~ o(￣▽￣)ブ sm-shaw
There are two questions
1、Upgrade from SQL server2008 to a higher version. The pressure test results are higher than those of the higher version installed directly
Test environment
same 24c96g
on the same machine
hammerdb4.7 same Configuration
1024 warehouses

SQL server2019 upgraded based on SQL server2008

SQL server2019 is installed directly

I have found through multiple tests that 512，128，64 virtual user pressure test results have little difference。And For the same virtual user the 2019 upgraded in 2008 is higher than the 2019 installed directly

64 virtual user

128 virtual user

512 virtual user

[kevinlewang]

Hi~ o(￣▽￣)ブ sm-shaw
You can test this scenario and repeat it yourself. My repeated tests have found that this problem is almost always present in this scenario。

[sm-shaw]

OK, so this is clearer and the question is, using HammerDB for v4.7 SQL Server 2019 upgraded from SQL Server 2008 performs better than a direct install of SQL Server 2019 and are asking why this is the case?

If so, then great! - this is exactly what HammerDB is for, to identify this type of scenario. So what you need to do is look at the database performance metrics (such as activity monitor in SMSS) to identify a difference between them when running the workload and identify the reason. It is likely to be a difference in configuration from the upgrade to the direct install.

Some general comments though are with 2 sockets of 24c cores/48 threads you should be able to achieve higher performance than you are seeing here, so it looks like some tuning will benefit.

Note this blog post is old and is not full applicable on more recent versions but can give some general guidance on the things to check. https://www.hammerdb.com/blog/uncategorized/hammerdb-best-practice-for-sql-server-performance-and-scalability/

In particular, there are a couple of things:
Firstly, there is a big dip in throughput that is almost certainly checkpoint related. So you should check your I/O performance - inactivity monitor check resource waits and data file I/O. If you are waiting for your disks, then you will not be able to fully utilize the CPU. Look at the logging resource wait in particular and how the transaction log is configured between the different installs and the checkpoint advice in the blog.
Secondly, also as covered in the blog post, look at the processor groups output when running the agent. Connections should be evenly allocated between processor groups when they connect, however with 2 sockets there is the NUMA impact (This is covered elsewhere in blogs such as this https://www.dbi-services.com/blog/sql-server-automatic-soft-numa-and-uneven-cpu-load/) On thing to try initially is under server properties/processors, try setting processor affinity manually rather than automatically. This may help balance the CPU load more evenly.