SQL Server Schema/Stored Procedures "Optimizing the database"

[sm-shaw]

In this White Paper from Principled Technologies running a HammerDB workload, there is the following section:

Optimizing the database:
Amazon Web Services (AWS) provided us with a script that modified the base TPC-C-like database. The script altered some row and page locking settings and modified some stored procedures to improve OPM results for any solution running the benchmark.

As the workload has been modified by AWS, the changes in this section should be evaluated and considered for adoption so that the workload being run by default is consistent.

[sm-shaw]

SQL Server Stored Procedures have been implemented as per Principled Technologies/AWS white paper with some minor modifications where profiling showed the changes impacted performance.
Testing has been done on desktop and laptop only with both in-memory and default versions of the Stored Procedures with the results below including profiling that does have some performance impact.
Initial testing suggests that the changes improve performance.

Proposed changes are now open to comments/suggestions/modifications.

SQL Server Developers Edition 15.0.2080.9
Haswell i7 Desktop

CURRENT v4.4 SQL SERVER STORED PROCEDURES/SCHEMA

In-memory
Vuser 1:4 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 110776 NOPM from 259405 SQL Server TPM
Vuser 1:Gathering timing data from Active Virtual Users...
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
>>>>> SUMMARY OF 4 ACTIVE VIRTUAL USERS : MEDIAN ELAPSED TIME : 239323ms
>>>>> PROC: NEWORD
CALLS: 434379	MIN: 0.249ms	AVG: 0.862ms	MAX: 138.281ms	TOTAL: 374353.281ms
P99: 3.135ms	P95: 1.359ms	P50: 0.740ms	SD: 10416.603	RATIO: 39.106%
>>>>> PROC: PAYMENT
CALLS: 435082	MIN: 0.262ms	AVG: 0.768ms	MAX: 138.132ms	TOTAL: 334212.706ms
P99: 2.869ms	P95: 1.287ms	P50: 0.648ms	SD: 9546.928	RATIO: 34.912%
>>>>> PROC: DELIVERY
CALLS: 43374	MIN: 0.311ms	AVG: 3.759ms	MAX: 164.189ms	TOTAL: 163038.300ms
P99: 40.123ms	P95: 9.730ms	P50: 2.205ms	SD: 66233.490	RATIO: 17.031%
>>>>> PROC: SLEV
CALLS: 43396	MIN: 0.398ms	AVG: 0.656ms	MAX: 17.612ms	TOTAL: 28451.751ms
P99: 1.447ms	P95: 0.910ms	P50: 0.592ms	SD: 3675.576	RATIO: 2.972%
>>>>> PROC: OSTAT
CALLS: 43353	MIN: 0.241ms	AVG: 0.644ms	MAX: 22.768ms	TOTAL: 27942.223ms
P99: 1.637ms	P95: 1.016ms	P50: 0.590ms	SD: 3856.668	RATIO: 2.919%
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
Default
Vuser 1:4 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 89296 NOPM from 205474 SQL Server TPM
Vuser 1:Gathering timing data from Active Virtual Users...
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
>>>>> SUMMARY OF 4 ACTIVE VIRTUAL USERS : MEDIAN ELAPSED TIME : 239640ms
>>>>> PROC: NEWORD
CALLS: 366391	MIN: 0.439ms	AVG: 1.024ms	MAX: 4929.960ms	TOTAL: 375064.279ms
P99: 3.206ms	P95: 1.811ms	P50: 0.844ms	SD: 84665.270	RATIO: 39.128%
>>>>> PROC: PAYMENT
CALLS: 366529	MIN: 0.350ms	AVG: 0.940ms	MAX: 4933.817ms	TOTAL: 344707.784ms
P99: 3.182ms	P95: 1.767ms	P50: 0.733ms	SD: 117222.797	RATIO: 35.961%
>>>>> PROC: DELIVERY
CALLS: 36766	MIN: 1.254ms	AVG: 2.864ms	MAX: 322.127ms	TOTAL: 105283.707ms
P99: 6.769ms	P95: 4.662ms	P50: 2.603ms	SD: 22640.865	RATIO: 10.984%
>>>>> PROC: SLEV
CALLS: 36623	MIN: 0.623ms	AVG: 1.913ms	MAX: 829.529ms	TOTAL: 70056.151ms
P99: 3.136ms	P95: 1.996ms	P50: 1.235ms	SD: 165199.733	RATIO: 7.308%
>>>>> PROC: OSTAT
CALLS: 36571	MIN: 0.220ms	AVG: 1.041ms	MAX: 699.098ms	TOTAL: 38088.576ms
P99: 2.327ms	P95: 1.482ms	P50: 0.731ms	SD: 90427.633	RATIO: 3.974%
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

UPDATED SQL SERVER STORED PROCEDURES/SCHEMA

In-memory
Vuser 1:4 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 156797 NOPM from 364879 SQL Server TPM
Vuser 1:Gathering timing data from Active Virtual Users...
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
>>>>> SUMMARY OF 4 ACTIVE VIRTUAL USERS : MEDIAN ELAPSED TIME : 239216ms
>>>>> PROC: NEWORD
CALLS: 624502	MIN: 0.286ms	AVG: 0.649ms	MAX: 133.608ms	TOTAL: 405374.706ms
P99: 1.841ms	P95: 0.971ms	P50: 0.575ms	SD: 6260.936	RATIO: 42.365%
>>>>> PROC: PAYMENT
CALLS: 625548	MIN: 0.340ms	AVG: 0.620ms	MAX: 133.886ms	TOTAL: 388055.341ms
P99: 1.866ms	P95: 0.934ms	P50: 0.545ms	SD: 7039.762	RATIO: 40.555%
>>>>> PROC: DELIVERY
CALLS: 63079	MIN: 0.925ms	AVG: 1.318ms	MAX: 67.994ms	TOTAL: 83150.077ms
P99: 3.076ms	P95: 1.746ms	P50: 1.217ms	SD: 7806.560	RATIO: 8.690%
>>>>> PROC: OSTAT
CALLS: 62916	MIN: 0.190ms	AVG: 0.382ms	MAX: 8.692ms	TOTAL: 24051.461ms
P99: 0.899ms	P95: 0.550ms	P50: 0.351ms	SD: 2530.966	RATIO: 2.514%
>>>>> PROC: SLEV
CALLS: 62499	MIN: 0.184ms	AVG: 0.287ms	MAX: 7.998ms	TOTAL: 17935.688ms
P99: 0.786ms	P95: 0.431ms	P50: 0.247ms	SD: 2342.356	RATIO: 1.874%
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
Default
Vuser 1:4 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 172268 NOPM from 400175 SQL Server TPM
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
>>>>> SUMMARY OF 4 ACTIVE VIRTUAL USERS : MEDIAN ELAPSED TIME : 239128ms
>>>>> PROC: NEWORD
CALLS: 687801	MIN: 0.339ms	AVG: 0.592ms	MAX: 1046.734ms	TOTAL: 406942.921ms
P99: 1.158ms	P95: 0.794ms	P50: 0.535ms	SD: 24088.214	RATIO: 42.544%
>>>>> PROC: PAYMENT
CALLS: 687765	MIN: 0.330ms	AVG: 0.530ms	MAX: 1111.803ms	TOTAL: 364253.183ms
P99: 1.065ms	P95: 0.706ms	P50: 0.482ms	SD: 20482.156	RATIO: 38.081%
>>>>> PROC: DELIVERY
CALLS: 68618	MIN: 0.762ms	AVG: 1.052ms	MAX: 358.426ms	TOTAL: 72186.487ms
P99: 1.944ms	P95: 1.359ms	P50: 0.972ms	SD: 17631.067	RATIO: 7.547%
>>>>> PROC: OSTAT
CALLS: 68758	MIN: 0.189ms	AVG: 0.527ms	MAX: 554.345ms	TOTAL: 36260.497ms
P99: 0.711ms	P95: 0.545ms	P50: 0.321ms	SD: 79177.869	RATIO: 3.791%
>>>>> PROC: SLEV
CALLS: 68946	MIN: 0.308ms	AVG: 0.499ms	MAX: 470.968ms	TOTAL: 34430.981ms
P99: 0.714ms	P95: 0.564ms	P50: 0.416ms	SD: 40634.671	RATIO: 3.600%
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

SQL Server Express Edition 15.0.2000.5
Icelake i5 laptop

Default
Vuser 1:4 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 103649 NOPM from 240479 SQL Server TPM
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
>>>>> SUMMARY OF 4 ACTIVE VIRTUAL USERS : MEDIAN ELAPSED TIME : 239752ms
>>>>> PROC: NEWORD
CALLS: 454184	MIN: 0.295ms	AVG: 0.863ms	MAX: 4595.277ms	TOTAL: 391794.841ms
P99: 2.913ms	P95: 1.449ms	P50: 0.690ms	SD: 83279.657	RATIO: 40.854%
>>>>> PROC: PAYMENT
CALLS: 453618	MIN: 0.270ms	AVG: 0.768ms	MAX: 2019.992ms	TOTAL: 348216.676ms
P99: 2.234ms	P95: 1.273ms	P50: 0.645ms	SD: 42668.858	RATIO: 36.310%
>>>>> PROC: OSTAT
CALLS: 45042	MIN: 0.165ms	AVG: 1.575ms	MAX: 4822.318ms	TOTAL: 70957.873ms
P99: 1.873ms	P95: 1.002ms	P50: 0.442ms	SD: 544326.131	RATIO: 7.399%
>>>>> PROC: DELIVERY
CALLS: 45256	MIN: 0.670ms	AVG: 1.530ms	MAX: 1085.554ms	TOTAL: 69254.568ms
P99: 5.744ms	P95: 2.761ms	P50: 1.171ms	SD: 84767.214	RATIO: 7.221%
>>>>> PROC: SLEV
CALLS: 45612	MIN: 0.279ms	AVG: 0.888ms	MAX: 2094.100ms	TOTAL: 40507.048ms
P99: 2.748ms	P95: 1.215ms	P50: 0.574ms	SD: 171020.204	RATIO: 4.224%
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

[b-anup]

Does that mean that this new changes to optimize the database looks good and is included in the latest build?

[sm-shaw]

Yes, that is correct. All changes for v4.5 are now committed in the master branch so the SQL Server stored procedures in v4.5 will be the ones in this White Paper from Principled Technologies to make them compatible with other cloud tests/publications.

v4.5 will now be built, tested and released with a planned GitHub release date of early August.

Note that for earlier access anyone can build from source on Linux or Windows by cloning/downloading the current master branch and following the instructions here: https://www.hammerdb.com/blog/uncategorized/how-to-build-hammerdb-from-source/
The v4.5 documentation is also in the master branch so this can be read with a Docbook/XML viewer https://github.com/TPC-Council/HammerDB/tree/master/DocBook

So you have everything you need before the actual GitHub Release (just one of the benefits of open source)