In Part 5 an issue with the cost estimator has been solved and the HammerDB workload runs much faster. But what to tune now? Let’s give the Database Engine Tuning Advisor a chance for this performance tuning.

Phase 3 – Forming a hypothesis – Part 4

Based on observation and declaration form a hypothesis

Based on observation and the lessons I learned I believe the TPM/NOPM values should increase if we further tune SQL statements with the help of the Database Engine Tuning Advisor

Phase 4 – Define an appropriated method to test the hypothesis

4.1 don’t define too complex methods

4.2 choose … for testing the hypothesis

the right workload

original workload

the right metrics

In this case I concentrate only on the TPM/NOPM values.

some metrics as key metrics

TPM/NOPM

the right level of details

an efficient approach in terms of time and results

Adding indexes may take 1h

a tool you fully understand

The Database Engine Tuning Advisor will be used to analyze the plan cache and this tool will provide some advises how to introduce indexes, partitions etc.

Just an approximation. There are so much variables even in this simple environment that this would take too much time. The approximation shows that as long I don’t make changes myself to the environment the results should be stable.

concentrate on key metrics

so no measurable changes

is the result statistically correct?

No. The selection was only one point in time. I repeated the test a few times with a similar result, but still no.

Phase 7 – Conclusion

Is the goal or issue well defined? If not go back to “Phase 1.1”

7.1 Form a conclusion if and how the hypothesis achieved the goal or solved the issue!

The hypothesis could not really tested. The Database Engine Advisor Engine provided changes which are not in line with the TPC-C so we could not really tune SQL Statements. BUT when there are no more options left to tune I will introduce new indexes.

7.2 Next Step

Is the hypothesis true?

Not evaluated

if goal/issue is not achieved/solved, form a new hypothesis.

I will form a new hypothesis in the next post of this series. The end of the video showed that we should give Flashsoft another try to improve the disk latency.