Skip to content

Load Tests#

Improving the time to perform the batch recalculation of a scenario requires balancing resources correctly. Adding additional process servers can greatly improve the time to finish a job, but without properly balancing this increase with corresponding SQL Server resources a degradation in performance could occur. Since every environment configuration can be different, use these concepts as a guideline to help provide a starting point to planning for your environment, and adjust as needed.

Info

The average calculation time for an asset on an individual basis can vary. For consistency in the testing results, a single asset duplicated was utilized, ensuring a baseline to work from. If the property asset types primarily used vary from the standard Office property, then adjust performance expectations for average calculation time and total job time according to what you experience in your environment. Additionally, calculation time will vary based on the analysis years used, the complexity of the model, additional modeling features used, and other variables. For testing an analysis period of 10 years was selected.

Impact of Adding Process Servers#

In a series of tests performed, there was a clear impact of scaling SQL server resources appropriately on the addition of process servers.

Report on Timing of Tests

The test results were for a database of 138GB, with a recalculation job performed against 500 assets.

SQLCpu Threads SqlRAM ProcServer MaxCpuPercent MaxIOPS TestDuration
2 12 8GB 2 85 % 1,954 30 min
2 24 8GB 4 93 % 2,334 29 min
2 36 8GB 6 93 % 3,415 28 min
4 12 16GB 2 64 % 2,528 21 min
4 24 16GB 4 79 % 2,782 14 min
4 36 16GB 6 85 % 3,007 13 min
8 12 32GB 2 69 % 3,798 20 min
8 24 32GB 4 85 % 3,358 12 min
8 36 32GB 6 79 % 3,741 8 min

Test Findings#

When SQL Server is allocated only 8GB ram and 2 cores, addition of more process servers has little impact on the overall job duration. This indicates SQL Server is resource constrained and not able to benefit from the additional threads working to save to the database.

When SQL Server is allocated additional resources (16GB ram and 4 cores) the bottleneck is lifted quickly and the time to process the requested calculation job quickly is dropped. However, scaling up to 6 process servers shows once again that very little gain is obtained from the addition of a new process server. The timing from 4 process servers to 6 shows the lack of a significant gain in queued job duration.

Finally, once resources are more generously allocated, SQL server no longer is bottlenecking the ability of the process servers to proceed, and noticeable gain for each additional allocation of process servers is shown.

Tip

The tests indicate the importance of ensuring that SQL Server is scaled up sufficiently with the consideration of additional process servers. Merely adding process servers can be detrimental overall to performance without this being kept in mind.

Report on Timing Per Asset

Report on Timing Per Asset#

Minimum SQLCpu Average Maximum Threads SqlRAM ProcServer Title
18 seconds 4 26 seconds 1 minute 12 16 2 Baseline-2Proc-RAM16GB-SqlCPU4
15 seconds 8 26 seconds 1 minute 12 32 2 Baseline-2Proc-RAM32GB-SqlCPU8
20 seconds 2 37 seconds 1 minute 12 8 2 Baseline-2Proc-RAM8GB-SqlCPU2
20 seconds 4 44 seconds 2 minutes 24 16 4 Baseline-4Proc-RAM16GB-SqlCPU4
18 seconds 8 36 seconds 1 minute 24 32 4 Baseline-4Proc-RAM32GB-SqlCPU8
48 seconds 2 1 minute 3 minutes 24 8 4 Baseline-4Proc-RAM8GB-SqlCPU2
21 seconds 4 53 seconds 2 minutes 36 16 6 Baseline-6Proc-RAM16GB-SqlCPU4
18 seconds 8 33 seconds 1 minute 36 32 6 Baseline-6Proc-RAM32GB-SqlCPU8
58 seconds 2 2 minutes 6 minutes 36 8 6 Baseline-6Proc-RAM8GB-SqlCPU2