6. SQL Server Performance Tuning study with HammerDB – Database Engine Tuning Advisor

SQL Server performance study with HammerDB

full course
  1. 1. SQL Server Performance Tuning study with HammerDB – Setup SQL Server
  2. 2. SQL Server Performance Tuning study with HammerDB – Setup HammerDB
  3. 3. SQL Server Performance Tuning study with HammerDB – Using 8PP – Part1
  4. 4. SQL Server Performance Tuning study with HammerDB – solving ACCESS_METHODS_DATASET_PARENT
  5. 5. SQL Server Performance Tuning study with HammerDB – TOP 10 most costly SQL statements
  6. 6. SQL Server Performance Tuning study with HammerDB – Database Engine Tuning Advisor
  7. 7. SQL Server Performance Tuning study with HammerDB – Flashsoft and PX600 unleash the full power
  8. 8. SQL Server Performance Tuning study with HammerDB – Solve PAGEIOLATCH latch contention

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.
  • 4.3 document the defined method and setup a test plan

  I will run the following test plan and analyzing:

Test plan 1

Run the Database Engine Tuning Advisor and analyze the plan cache (last 1000 events)

Analyze the report

Maybe create indexes / partitions/ stats. etc

Start HammerDB workload

Stop HammerDB workload and compare this run with the baseline

Phase 5 – Testing the hypothesis – Test Plan 1

  • 5.1 Run the test plan

    • avoid or don’t test if other workloads are running
    • run the test at least two times

I recorded a video when running the test plan 1.  So as long as I stay in line with the TPC-C rules the Database Engine Tuning Advisor optimization is only related to creating new statistics.

  •  5.2 save the results
    • All results are saved into the log files

Phase 6 – Analysis of results – Test Plan 1

  • 6.2 Read and interpret all metrics
    • understand all metrics
    • compare metrics to basic/advanced baseline metrics
      • TEST RESULT NOW : System achieved =161882 SQL Server TPM at = 35150 NOPM
      • TEST RESULT BEFORE: System achieved 161091 SQL Server TPM at = 35013 NOPM
    • has sensitivity analysis been done?
      • 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.
  • 6.3 Visualize your data
  • 6.4 “Strange” results means you need to go back to “Phase 4.2 or 1.1
    • nothing strange
  • 6.5 Present understandable graphics for your audience
    • Done.

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.

One thought on “6. SQL Server Performance Tuning study with HammerDB – Database Engine Tuning Advisor

Leave a Reply

Your email address will not be published.