SAP HANA Performance Optimization FAQs

Which are the prerequisites should be fulfilled for performing a detailed performance analysis?

The performance analysis capabilities significantly improve with the latest SAP HANA patch levels (e.g. more comprehensive history information, utilization of STATEMENT_HASH for SQL statements), so it is of advantage to have a recent SAP HANA patch level in place.

User should ensure that the SAP HANA parameters are all set based on the SAP recommendations because in this manner the user ensures that important monitoring features have all been enabled, e.g. the expensive statement trace and the thread sample details.

As of SAP HANA Revision 74 it is suggested switching to the embedded statistics server). Among others this change permits storing critical thread sample information for longer times in HOST_SERVICE_THREAD_SAMPLES.

In order to ensure that sufficient historic information has been placed and also comparisons with activities of the previous month are all possible (e.g. comparison of current month's end closing activities with previous month's end closing activities) the statistics server history retention should ideally be set to 42 days as suggested recommended in SAP. User should be aware that this change upsurges the memory requirements of the statistics server.

How can the user verify if a performance issue is related to SAP HANA?

In case of problems with performance it is important that the user understands at first in which area the regression happens. For instance, if the user performs a migration to SAP HANA and the resulting performance is not as good as expected, the user tends to suspect that SAP HANA is responsible. In a lot of real-life cases this assumption is not accurate and the regression was introduced for various reasons, e.g. because of the SAP kernel or due to an increased network latency.

For avoiding the unnecessary analysis on SAP HANA side user should first identify the portion of time spent on SAP HANA server side, on application side (e.g. within ABAP) and in the network between SAP HANA and the application. Incase, the SAP HANA server side is responsible for a noteworthy fraction of the overall response time, then in that case a more detailed investigation on SAP HANA side is useful.

If the user has upgraded to SAP kernel 7.4x, then critical performance regressions can be triggered by this change instead of by SAP HANA.

How can the user identify the main contributors to the SAP HANA response time?

The user needs to understand where most of the time is lost, to be able to focus on the most important performance factors. On the SAP HANA server side this time distribution can be identifiable on the THREAD_STATE (and in case of locks LOCK_WAIT_NAME) information in views M_SERVICE_THREADS and M_SERVICE_THREAD_SAMPLES.

Which are the various types of SAP HANA performance views which exist?

The following listed are the sources with performance information exist:

Source	Location	Details
*M_ Views**	Memory	Monitoring views entails the information since the previous startup
*M__RESET Views**	Memory	Resettable counterparts of monitoring views Comprises of the information since last reset A manual reset can be done by the following command listed below: ALTER SYSTEM RESET MONITORING VIEW <reset_view> User needs to have the RESOURCE ADMIN privilege to execute this command
*HOST_ and GLOBAL_* Views**	Memory + Disk	The statistics server populates the History views

List the SAP services available for analyzing and optimizing the performance of SAP HANA?

The most critical service for analyzing and optimizing the performance of SAP HANA is listed below:

SAP Technical Performance Optimization - HANA (TPO)

SAP collects the runtime and performance data, current architecture, and later identifies issues and risks related to SAP HANA as a part of this service and analyzes the longest running SQL statements. It also entails an empowering component, i.e. a workshop with a joint SAP HANA analysis and detailed technical discussions. Refer to SAP Note 2177604 for more details.

Various other SAP services also comprise of SAP HANA performance analysis:

Volume Test Optimization Service (VTO)
Business Process Performance Optimization Service (BPPO)
SAP GoingLive Support Service (GLS)
SAP EarlyWatch Check
SAP HANA System Administration Service
Solution Manager Self Service "SQL Statement Tuning" (SAP Note 1601951)
Customer Program Optimization Service (CPO)

Incase SAP HANA completely hangs up, then what all should be factored in?

While a database performance issue is generally local and permits the users for performing analysis, a hang situation prevents every access to SAP HANA. Both the local performance issues and hang situations can be linked to the same root causes, therefore the checks described in "Which general checks can be performed in order to optimize the performance of the SAP HANA database?" should also be duly factored in during a hang situation.

How can the user optimize the performance of DDL operations?

For the performance of DDL operations same set of rules are applicable for the normal SQL statements. The table listed below lists the specific details which are quite particularly helpful for few DDL operations:

DDL type	Optimizations
CREATE INDEX	Consider the following optimizations: If possible, create the index before the underlying table is populated with data. For large tables make sure multi-threaded execution is used. This can be enforced by setting the parameter indexserver.ini -> [joins] -> single_thread_execution_for_partitioned_ tables to 'false'. In case of a column store table and a unique / primary index it is helpful to perform a delta merge (SAP Note 2057046) and make sure that the delta storage remains empty while the index is created. This eliminates the need to combine main and delta dictionaries required for uniqueness check purposes.

DDL type

Optimizations

CREATE INDEX

Consider the following optimizations:

If possible, create the index before the underlying table is populated with data.
For large tables make sure multi-threaded execution is used. This can be enforced by
setting the parameter indexserver.ini -> [joins] -> single_thread_execution_for_partitioned_
tables to 'false'.
In case of a column store table and a unique / primary index it is helpful to perform a delta merge (SAP Note 2057046) and make sure that the delta storage remains empty while the index is created. This eliminates the need to combine main and delta dictionaries required for uniqueness check purposes.

How can a representative workload be replayed in a different environment?

Optimal testing of a system change like a SAP HANA upgrade or a modification of the hardware layout requires the possibility to replay a representative workload in the new environment. Starting with SAP HANA SPS 12 the built-in tool Capture and Replay is available for that purpose.

How can automatic actions be triggered in case of bad performance?

It is quite important to collect additional information like runtime dumps ) times of an unresponsive or slow system in order to make sure that the later root cause analysis can be performed successfully. You can schedule SAP HANASitter ) in order to make sure that analysis data is collected during suspicious time frames.

List the reasons for a non-responsive SAP HANA database without an obvious resource or a locking issue?

Generally in most cases a slow or hanging SAP HANA database is triggered by lock scenarios or a high resource consumption). Nevertheless there maybe be scenarios where the database is either stuck or slow the user can neither find the obvious locks nor high resource consumption:

Scenario	Details
Wrong parameter settings	Wrong parameters (e.g. a very small max_concurrency value or a restricted affinity setting) can considerably confine the resources which can be used by SAP HANA so the user must ensure that all the parameters have been configured based on SAP best practices.
Call stack generation via gstack	If user is using gstack for generating SAP HANA call stacks, it can block the entire SAP HANA instance while it is executed. Neither the CPU consumption is increased nor are there any dedicated locks visible. All the activities have been suspended until the gstack is over and completed.