MS SQL 2014 - Scatter of results

[everande]

Hello,

What could be the reason for the non-repeatability of the results?

I have a new server. There is only one VM to use with HammerDB, so HammerDB is the only one workload. I have made three tests - each time restore tpcc database, restart the SQL service to have empty RAM, and so on. I'm using 12 cores (threads). The results looks like below - each time results are quite different when more users are working on database:

[sm-shaw]

There are a wide number of reasons to why you could see variability in the workload. First of course, you are looking at an average of the TPM over a measured period of time. So the first thing you should do is to check the throughput at the higher core count to see if the transaction count is relatively level or whether you see peaks and troughs indicating contention. If you see contention such use a tool such as activity monitor and identify your top wait events.

[everande]

OK, I can see something like this:

There is continuous LOG file write only. Periodically there is data write do ndf files which reduces the number of transactions. I'm wondering why in some trial this is more often than in other (same disk, same data file location, same begnining conditions)

[sm-shaw]

Great - this is called a checkpoint
https://docs.microsoft.com/en-us/sql/relational-databases/logs/database-checkpoints-sql-server?view=sql-server-ver15
There is a tips/best practice document, please note that this quite old and not all of the advice is up to date but it gives guidance on checkpoints here:
https://www.hammerdb.com/blog/uncategorized/hammerdb-best-practice-for-sql-server-performance-and-scalability/

[everande]

Thanks for your patience ;)

The checkpoint isn't maybe so strange, rather why once have impact and next time not - only this :)

BTW
About SQL server / DB configuration for HammerDB... I'm using configuration and properties similar to my production enviroment. Is optimising DB for HammerDB are right? The is no about to have higher result - higher number but real production databese performance, is it? At the end production DB can have different configuration forced by application spec or production app best practices

[sm-shaw]

The checkpoint is not unusual at all, when you are running multiple workloads, follow the guidance above and set DBCC trace 3502, 3504 and 3605 to see them occurring in the error log.

For optimized vs production performance, this is one of the most common beginning errors. Optimized performance is generic, whereas production performance is very precise and only relevant to one scenario, preventing direct comparison between systems. For one example, synchronous commit is always used in production, however using asynchronous commit will permit higher CPU utilization on a lower performing transaction log disk drive to simulate an environment where you have spent more on I/O capabilities. Significant investment in a production environment will give results similar to the optimized one but cost you less. The optimized results 'sets the bar' for which performance should never be exceeded in the production environment.

I would recommend start with an optmized configuration then test the production settings to measure the impact of the production configuration and find where you could invest more for better performance in CPU, memory and I/O.

[everande]

thanks for all your replies - very helpful