4. SQL Server Performance Tuning study with HammerDB – solving ACCESS_METHODS_DATASET_PARENT

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 the last post we found that the bottleneck seems not related to the disc access latency. So I disabled Flashsoft 3.7 for now. The next part in this performance tuning study will show how we solve the wait events for ACCESS_METHODS_DATASET_PARENT.

Phase 3 – Forming a hypothesis – Part 2

  • Based on observation and declaration form a hypothesis
    • Based on observation and the last ShowIOBottlenecks run I believe: “The TPM/NOPM should be increasing if the wait events  Intra Query Parallelism – ACCESS_METHODS_DATASET_PARENT will be reduced/resolved”

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
    • some metrics as key metrics
    • the right level of details
    • an efficient approach in terms of time and results
      • approx. 10 min
    • a tool you fully understand
  • 4.3 document the defined method and setup a test plan

  I will run the following test plan and analyzing:

Test plan 1

This post by sqlskills points out that the ACCESS_METHODS_DATASET_PARENT I should investigate the MAX_DOP setting

I will check the Max Degree of Parallelism setting which should be 0 per default and set it to a value of 2. Because this server has HT active and we got 2 active cores.

Microsoft shows how to set the Max Degree of Parallelism for this instance.

I will run ShowIOBottlenecks while the HammerDB workload is running with Max Degree of Parallelism set to 2.

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 checked the actual Max Degree of Parallelism with this query:

max degree of parallelism03276700

Then I set the Max Degree of Parallelism to 2:

The output has been:

I started the HammerDB workload again like shown in this video.

  •  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
      • The important metrics ShowIOBottlenecks showed that from a wait event view there are two main phases when running:
      • This one shows that CPU and Threading seems to be the bottleneck and the disk is not saturated.SQLServer2014_MaxDegreePar_active_CPU
      • This one shows that I/O WRITELOG seems to be the bottleneck and CPU still got a ~30% wait time.SQLServer2014_MaxDegreePar_active_WriteLog
  • compare metrics to basic/advanced baseline metrics
    • TEST RESULT NOW : System achieved 20227 SQL Server TPM at 4443 NOPM (Best run)
    • TEST RESULT IN THE Beginning: System achieved 20820 SQL Server TPM at 4530 NOPM
  • 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.
  • 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
    • The ACCESS_METHODS_DATASET_PARENT is sometimes still arising (<=10%) but it seems to be solved for now.
  • 6.3 Visualize your data
    • The screen-shots will do the job.
  • 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 has been proven wrong. The TPM=20170 and NOPM=4336 reached shows that solving the ACCESS_METHODS_DATASET_PARENT seems to have nearly no influence at the moment.
    • The reasons why the hypothesis is wrong:
      • It seems we solved this bottleneck but it seems there is another issue which we did not discover yet.
      • The setting MAX DOP to 2 will be used in this case because it is recommend by Microsoft.
  • 7.2 Next Step
    • Is the hypothesis true?
      • No. 
      • if goal/issue is not achieved/solved, form a new hypothesis.
        • I will form a new hypothesis in the next post of this series. And believe me. The TPM and NOPM values will increase 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *