7. SQL Server Performance Tuning study with HammerDB – Flashsoft and PX600 unleash the full power

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

I solved all bottlenecks since we started this performance tuning study. But now I can’t find any improvements which can be done without altering the schema or indexes which is not allowed by TPC-C rules. It is a similar situation when you run a third party application with a database which you are not allowed to change. A great solution to improve the disk latency is caching based on Flash, because it is transparent to the application vendor. The advantage of Flashsoft 3.7 is that it provides a READ and WRITE cache. The write cache is the one which should help with this OLTP workload. Remember Flashsoft can cache FC,iSCSI,NFS and local devices.

Phase 3 – Forming a hypothesis – Part 5

  • 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 the disc access latency will be reduced with the use of READ/WRITE cache (Flashsoft).

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
      • Installing and configuring Flashsoft will take 30 min
    • a tool you fully understand
  • 4.3 document the defined method and setup a test plan

  I will run the following test plans:

Test plan 1

Implement a READ/WRITE cache for SQL Server based on Samsung 840 basic

Start HammerDB workload

Run ShowIOBottlenecks and Resource Monitor

Stop HammerDB workload and compare this run with the baseline

Analyze the ShowIOBottlenecks

Test plan 2

If ShowIOBottlenecks still shows wait events for disk latency I will use the PX600-1000 as READ/ WRITE cache device

Start HammerDB workload

Run ShowIOBottlenecks and Resource Monitor

Stop HammerDB workload and compare this run with the baseline

Phase 5 – Testing the hypothesis – Test Plan 1+2

  • 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.

  • System achieved 338486 SQL Server TPM at 73651 NOPM
  • System achieved 314510 SQL Server TPM at 68320 NOPM
  • System achieved 313778 SQL Server TPM at 68256 NOPM

the Resource Monitor showed that there is still latency around 10ms and ShowIOBottlenecks shows that there are wait events for Write log. So I decided to use the PX600-1000.

I recorded a video when running the test plan 2.

  • System achieved 338989 SQL Server TPM at 73685 NOPM
  • System achieved 348164 SQL Server TPM at 75689 NOPM
  • System achieved 336965 SQL Server TPM at 73206 NOPM
  •  5.2 save the results
    • All results are saved into the log files

Phase 6 – Analysis of results – Test Plan 1+2

  • 6.2 Read and interpret all metrics
    • understand all metrics
    • compare metrics to basic/advanced baseline metrics
      • TEST RESULT Flashsoft Samsung 840:
        • System achieved (338486,314510,313778)=322258 SQL Server TPM at (73651,68320,68256)=  70076 NOPM
      • TEST RESULT Flashsoft SanDisk PX600-1000:

        • System achieved (338989,348164,336965)= 341373 SQL Server TPM at  (73685,75689,73206) = 74193 NOPM
      • TEST RESULT before:
        • System achieved =161882 SQL Server TPM at = 35150 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 to the environment  the results should be stable.
    • concentrate on key metrics
      • While using Flashsoft with Samsung Basic 840 or SanDisk PX600-1000 I could nearly double the performance compared to the last run.
    •  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 is true. I doubled the performance while I make use of the Flashsoft caching solution. I found that there is only a small difference between the Samsung and the SanDisk drive. SanDisk PX600-1000 should be much faster than the consumer SSD. The reason seems to be a new bottleneck I found. Page Latch waits are involved!
  • 7.2 Next Step
    • Is the hypothesis true?
      • Yes
      • if goal/issue is not achieved/solved, form a new hypothesis.
        • I will form a new hypothesis in the next post of this series where I’ll track down the Page Latch wait events and solve them.

10 thoughts on “7. SQL Server Performance Tuning study with HammerDB – Flashsoft and PX600 unleash the full power

Leave a Reply

Your email address will not be published.