HammerDB v4.10 adds performance enhancements for the SQL Server TPROC-H workload in the form of partitioning for the orders and lineitem tables as well as the option to create advanced statistics.
Enabling Partitioning and Advanced Statistics
These can be selected with a checkbox in the GUI.
or the following options being set to true in the CLI.
mssqls_tpch_partition_orders_and_lineitems = true mssqls_tpch_advanced_stats = true
Benefits of Partitioning and Advanced Statistics
When partitioning is selected both the order and Lineitem tables are partitioned with each partition spanning 1 week. By default t partition flag is set to false by default and must be enabled. Both Column Store and Row store are allowed to be partitioned. When partioning is enabled. Certain primary and foreign key definitions defined in HammerDB are no longer valid and will not be created. However HammerDB will still create those keys/constraints when the Orders and Lineitem tables are not partitioned.`
For columnstore indices, the is an initial degradation of performance, however if the user calls Alter Index … Reorganize or leaves the server running after the initial load for approximately 48 hours, the performance of power runs improves by 2X when compared to column store indices with no partitioning.
The advanced statistics option is also set to off by default, and when enabled it does increase the overall load time however it creates an option for users loading TPCH for MSSQL to perform a more advanced statistical analysis to benefit query performance.
Summary
HammerDB v4.10 introduces an option to partition the SQL Server TPROC-H schema and perform a more advanced statistical analysis, taking advantage of these options can benefit query performance.