HammerDB recommends the following few best practices for Microsoft SQL Server:
- Configure max degree of parallelism to 1.
- Configure max worker threads between 900 to 3000. Subject to change according to the available logical processor. Higher CPU can accommodate higher worker threads.
- Keep the initial data file size sufficiently large to ensure that files are not growing rapidly in the middle of a workload run.
- Auto growth settings should be configured correctly so as the file grows sufficiently without any performance impact.
For more information about the HammerDB best practices, see HammerDB documentation.
HammerDB also recommends a few settings in the workload tool to generate more transactional workload:
- Use the ‘all warehouses’ option in HammerDB to spread the transactions across all the datasets. This prevents a workload contention over just a few tables in the dataset.
- Select checkpoint when complete option to force the database to write everything to the disk at the end of the test. This is useful when running multiple tests in a row to avoid a checkpoint in the middle of a run.
- Use the autopilot option to run the multiple tests in a row when automation of multiple test cases is wanted.