In the upcoming weeks we will be posting new videos to the SAP HANA Academy to show new features and functionality introduced with SAP HANA Support Package Stack (SPS) 11.
The topic of this blog is Performance Monitoring.
No big changes In the domain of performance monitoring but several enhancements and improvements. A number of monitoring view have had columns added to provide more detailed information and facilitate troubleshooting. To further support this, three new paragraphs have been added to the SAP HANA Troubleshooting and Performance Analysis Guide: Common Symptoms and Troubleshooting, SQL Query Tuning Tips, and Troubleshooting System Replication.
Connections and Commits
The column CLOSE_REASON has been added to the view M_CONNECTIONS to display the reason why the session was closed, e.g. because the connection client has been terminated, or because of an idle timeout.
The maximum, total, and average execution time required for a commit, plus the commit count have been added to the view M_CONNECTION_STATISTICS.
Data Definition Language
A new monitoring view M_EXECUTED_STATEMENTS enables to report on all executed DDL (data definition language) statements. One of the reasons this view was added was to support root cause analysis of invalid metadata issues. It is enabled by default but can be turned off by setting the system property ‘enable_ddl’ to false, section [executed_statements] of global.ini. DDL statements are traced in memory, and flushed in regular intervals to a file. The size of this file and the flush interval are configurable, as well as the maximum number of trace files (parameters maxfilesize, maxfiles and trace_flush_internal).
The view M_SQL_PLAN_CACHE now contains columns MAX_CALLED_THREAD_COUNT, TOTAL_CALLED_THREAD_COUNT and AVG_CALLED_THREAD_COUNT to show how many threads have been used to execute a specific query, and the columns MAX_SERVICE_NETWORK_REQUEST_COUNT, TOTAL_SERVICE_NETWORK_REQUEST_COUNT and AVG_SERVICE_NETWORK_REQUEST_COUNT record TrexNet request counts.
The view M_EXPENSIVE_STATEMENTS now includes the column WORKLOAD_CLASS_NAME to show which workload class had been assigned to a specific statement execution.
The SQL statement hash is now included in trace files in case of error situations (e.g. out of memory situations) to easily identify which statement caused the problem.
Common Symptoms and Troubleshooting
A new paragraph, Common Symptoms and Troubleshooting, has been added to Chapter 2. Symptoms, SAP HANA Troubleshooting and Performance Analysis Guide. with topics
- Slow System-wide Performance
- Slow Individual SQL Statements
- Frequent Out of Memory (OOM)
SQL Query Tuning Tips
A new paragraph, SQL Query Tuning Tips, has been added to Chapter 3. Root Causes and Solutions, SAP HANA Troubleshooting and Performance Analysis Guide with topics
- Avoid Implicit Type Casting in Queries
- Avoid Inefficient Predicates in JOIN
- Avoid Inefficient Predicates in EXISTS/IN
- Avoid Set Operations
- Improve Performance For Multiple Column Join
- Using Hints to Alter a Query Plan
Troubleshooting System Replication
A new paragraph, Troubleshooting System Replication, has been added to Chapter 3. Root Causes and Solutions, SAP HANA Troubleshooting and Performance Analysis Guide with topics
- Check If Log Can Be Shipped In Time
- Check If Data Load Can Be Handled By Network Link
- Check Network Configuration (long distance)
- Check Disk I/O on Secondary Site
For performance monitoring, most changes with SAP HANA SPS 11 have been in the documentation: the SAP HANA Troubleshooting and Performance Analysis Guide.
For information about monitoring SAP HANA, please see the playlist SAP HANA Academy – Monitoring SAP HANA: Tools Overview – YouTube
- SAP HANA SQL and System Views Reference – SAP Library
- SAP HANA Troubleshooting and Performance Analysis Guide – SAP Library
- Common Symptoms and Troubleshooting – SAP HANA Troubleshooting and Performance Analysis Guide – SAP Library
- SQL Query Tuning Tips – SAP HANA Troubleshooting and Performance Analysis Guide – SAP Library
- Troubleshoot System Replication – SAP HANA Troubleshooting and Performance Analysis Guide – SAP Library
- 1813020 – How to generate a runtime dump on SAP HANA
- 2000002 – FAQ: SAP HANA SQL Optimization
- 1999997 – FAQ: SAP HANA Memory
Thank you for watching
You can view more free online videos and hands-on use cases to help you answer the What, How and Why questions about SAP HANA and the SAP HANA Cloud Platform on the SAP HANA Academy at youtube.com/saphanaacademy.
Follow us on Twitter @saphanaacademy
Connect with us on LinkedIn