Very poor result of HammerDB test with 300 TMP on Windows Server

[Jennify16]

Hello, I created a PowerShell script to automatically run the HammerDB test. It runs smoothly with the appropriate number of TMPs on many Windows servers. However, on one server, it received a very poor value of 300 TMP. I've tried many things to improve this abnormal result, but the result remains unchanged. Does anyone know what the cause might be?

[sm-shaw]

Firstly lets change this to a discussion topic ...

[sm-shaw]

So firstly you mean NOPM/TPM instead of TMP?

Secondly you don't give any hammerdb or database configuration, data, or results. You also don't even mention the database you are testing only it is on Windows - all 6 of the databases HammerDB supports run on windows. You say you've tried many things but don't say what any of these are. (This is why Issue templates have these sections for a reason). You also say you got an appropriate number of TPM but again not what you have decided is appropriate. So for anyone to know the cause without anything to work from would clearly be guesswork.

Nevertheless it is worth noting that this is exactly one of the scenarios that HammerDB is for, you've tested multiple databases and found one environment does not perform the same as the rest - many people do not even get to this stage.

So, a more appropriate question is not "Does anyone know what the cause might be" but by providing as much configuration and data as possible the correct question then is "based on these results, can anyone suggests the right steps to take to troubleshoot my database performance issues".

[Jennify16]

Thanks Steve for your correction.

Sorry, firstly I meant TPM instead of TMP.

Secondly I'm using HammerDB 5.0 with the same configurations to test the same TPCC database in SQL Server on different Windows Servers and to see if the automation works on different servers.

The results on different Windows servers are between 147,375 TPM and 180054 TPM, but only one server has 300 TPM.

On this server (300 TPM), I tried to uninstall and reinstall HammerDB, and also to uninstall and reinstall SQL Server, but it didn't help. The resources (CPU, RAM, etc.) for Windows Server are almost the same.

Based on these results, can anyone suggests the right steps to take to troubleshoot my database performance issues?

[sm-shaw]

Great question 😄

so there is a blog post here where I walk through troubleshooting database performance that was linked to a CPU microcode issue https://www.hammerdb.com/blog/uncategorized/diagnosing-cpu-faults-and-performance-with-hammerdb/ I'm not saying this is also your issue but it helps walk you through some of the steps you need to take.

So firstly look in the SQL Server logs and windows event logs - do you see any errors here?

Then use the script to check the CPU single-threaded performance in SSMS - is this similar in the low seconds or taking a lot longer - if longer check the CPU and BIOS settings. While there also check the reported CPU frequency in task manager and make sure at the BIOS the CPU is not running in powersave mode. If problems are not obvious there could be physical issues depending on who installed the CPU - if they overheat due to incorrect installation they will throttle back performance.

Also when you do a run check the transaction count and CPU metrics - see the examples, if you can't use the GUI in the CLI use tcstart and metstart to gather the data and view in the HammerDB web service. (Note you can also use mpstat standalone in the agent directory to watch the per - CPU utilisation better than the Windows utilities).

Is the transaction count steady at a low level or shows peaks and troughs? If peaks and troughs then its waiting for something. Check the storage, especially the transaction log disk and look in the SQL Server logs for delayed checkpoints. Also look in activity monitor for similar events related to slow disks.

Once you've checked the CPU and the disk, also check the memory configuration, task manager will show you the DIMM slots used, make sure if not all slots are populated the DIMMs are in the right slots, DIMMs not populated according to the correct rules can result in very low performance.

Note that if you are restricted to the CLI when you run tctstart and metstart use the web service to view the transaction and CPU data - this will update as you are running it.

From the referenced blog post you can see I am getting up to 1M NOPM on a desktop configuration which gives a guide to expected performance on a checked configuration.

And make sure you post the solution when you find it to help others. You would be surprised (or not) how many database servers have never been checked and go straight into production with configuration issues that have never been detected. So your approach is exactly what everyone should do with a new installation.