5. SQL Server Performance Tuning study with HammerDB – TOP 10 most costly SQL statements

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 wait events  ACCESS_METHODS_DATASET_PARENT. I learned a few points now! Tuning the wait events should not be the first step in tuning a database. Since ages it is recommended to tune a database workload starting by the application down to the hardware. The reason is obvious. The performance tuning factors you can get using a better hardware or optimizing your hardware is normally in a range between 5% to 100%. Tuning one SQL statement may increase the overall performance 10x , 50x  or maybe 1000x.

Phase 3 – Forming a hypothesis – Part 3

  • Based on observation and declaration form a hypothesis
    • Based on observation that the bottleneck is related to CPU and workload I believe: “The TPM/NOPM should be increasing if we improve the TOP 10 most costly SQL statements or at least the most costly of all”

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
      • total_worker_time for the most costly SQL statement will be declared as the key metric because the workload seems to be bounded by CPU
    • the right level of details
    • an efficient approach in terms of time and results
      • approx. 1 h
    • a tool you fully understand
      • Pinal Dave posted a nice script which I will use to list the TOP 10 most costly SQL statements and the used execution plan. I make use of the order by total_worker_time.
  • 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 TOP10 script.

Identify and analyze the most costly SQL statement of all (make use of the execution plan analyzer)

Tune the discovered SQL statement

Start HammerDB workload

Run the TOP10 script and confirm if the most costly SQL statement of all is improved

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 run the TOP10 Script:

SQLServer2014_TOP10_before

The most costly statement here is surprisingly a select statement. This is strange because the OLTP workload should most of the time try to update/insert something.

After a short Internet research I found this blog which showed that this is related to the changes of the SQL Server Query Optimizer cardinality estimation process.

So adding an Index or changing the Query Optimizer? I decide to change the database compatibility to the pre-SQL Server 2014 legacy CE. The right way would be to add an index or use the Trace Flag 948. But these changes would not stay in-line with TPC-C rules!

The execution plan for the SQL Select looks like this before the changes with a table scan of the stock table which costs a lot!:

SQL Server2 014 Execution plan before

I change the SQL Server Query Optimizer cardinality estimation for the tpcc database to pre-SQL Server 2014. After the change the sys.dm_exec_query_stats should be flushed.

SQL_Change_compatibily-level

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

I run the TOP10 script:

SQLServer2014_TOP10_after

As you can see there are still two phases while running. One which shows CPU nearly at 90% and low disk access. And another with high disk access (saturated) and low CPU involved.

I stopped the HammerDB workload and compare this run with the baseline

  •  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
      • sys.dm_exec_query_stats show that the most costly SQL statement. The select which has been an issue is:

      • compare metrics to basic/advanced baseline metrics
        • TEST RESULT NOW : System achieved (149811,160772,172690)=161091 SQL Server TPM at (32541,34909,37590)= 35013 NOPM
        • 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
        • total_worker_time for the most costly SQL statement has been reduced to 13.339.391 compared to 1.286.386.037 before. The statement is not in the TOP10 anymore.

           
  • 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 right. The TPM=161091 and NOPM=35013 reached shows that solving this bottleneck caused by the SQL Server Query Optimizer cardinality estimation seems to have a big influence. The performance increased around 7x!
  • 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 because I am pretty sure there is much more I can tune.

Leave a Reply

Your email address will not be published.