Skip to Content
Technical Articles
Author's profile photo Yi Heng Lee

How-to Review and Set SAP HANA Parameters Following Updated Recommendations

Disclaimer: Any views or opinions represented in this blog are personal, and do not represent the views of my employer. This article is mainly for knowledge sharing.

 

Hello there, I have always found myself in below situations during my career as consultant for SAP HANA specifically in projects for implementation, migrations/upgrades, and even daily operation:

  1. SAP HANA Parameters setup during initial installation, following SAP best practice or recommended value(s).
  2. Review HANA Parameters of existing systems in case of HANA Support Pack Stacks or HANA Revision update.

It could be of company own systems, managing systems for customers, or from migration project taking over from other vendors.

 

Generate Recommendations

It is recommended to have the latest copy of SQL scripts from note 1969700. Run the “HANA_Configuration_Parameters_<version>.txt” in HANA Studio SQL Query Console, or SAP HANA Cockpit Database Explorer.

If the system you are checking is a SAP HANA multitenant database containers (MDC), this can run in both System DB and Tenant DB for the specific recommendations.

Example on the result from the query below, exported into spreadsheet.

HANA_Configuration_Parameters-Results

HANA_Configuration_Parameters-Results

 

Analyze the Recommendations

It is always important to review the recommendations gathered from the checks.

Using back the earlier example from the result runs in System DB. Some details on how to read the columns returned from result of query check. I have put in screens from HANA cockpit Database Configurations tile, and HANA Studio Configuration tab for the familiarity to those who uses them.

FILE_NAME – The INI file where these parameters are configured.

SECTION – Section under the INI files.

PARAMETER_NAME – The parameters checked

CONFIGURED_VALUE – Current value set in the system)

RECOMMENDED_VALUE – Recommendations based on the query check, against note 2600030.

SAP_NOTE – The SAP note where more detailed information regarding the parameters, the recommended settings etc.

CONFIG_LAYER – Layers where parameter is set, such as DEFULT, SYSTEM, DATABASE, HOST

HANA_Configuration_Parameters%20Results

HANA_Configuration_Parameters Results

HANA%20Cockpit%20Reference

HANA Cockpit Database Configuration

HANA%20Studio%20Configuration

HANA Studio Configuration

 

In this example let’s look at below 2 parameters recommendations.

  • Parameter max_table_count_in_statement default at 4095 during installation, which preventing some of the HANA SQL script run ended with error due to -> 463: number of tables exceeds its maximum: 4095; or 463: number of tables exceeds its maximum: table count in statement exceeds its maximum:4095.
  • Parameter num_cores for preprocessor job queue which only available in HANA System DB services. In the RECOMMENDED_VALUE column “10 to 48 [10]” suggesting a value ranges between 10 to 48; and suggested to start with 10 as initial.

 

Another example for the similar query run on tenant Database, below recommendations are suggested in accordance with workload management, better monitoring, and control on the HANA resources for finer level of granularity etc.

  • Enabling resource tracking for memory and expensive statement monitoring using views M_SQL_PLAN_CACHE or M_EXPENSIVE_STATEMENTS.
  • Setting statement memory limit to prevent single statement over using available memory, due to possibilities like poor SQL query handling or memory leak.
  • Enabling monitoring of thread activities through view M_SERVICE_THREAD_SAMPLES.
  • Parameters for Garbage Collection Optimization on specific HANA revisions, such as garbage_collect_interval_s.

 

Another situation the query check and results would be helpful. If there is/are parameter(s) currently set in the system and might need to be revised. This could be case of post HANA upgrade i.e., from HANA 1.0 to HANA 2.0, or SP3 to SP4/SP5, or even minor revision update.

One example such as below parameter check_cancel_at_allocation which is a workaround set in earlier HANA revision and is fixed on higher revision, as mentioned in note 2092196.

  • Revisions <= 048.06 (SPS04)
  • Revisions <= 050.00 (SPS05)

These types of parameters should be review and unset them as a housekeeping measure after HANA updates.

 

Implement Recommendations / Fallback

After completed the analysis and concluded on the required parameters.

The SQL query for setting the parameters is also provided in the result column IMPLEMENTATION_COMMAND. This helps to set parameters at once or set for similar system using same commands.

The other column UNDO_COMMAND from the result provided the query to unset/reset the parameters, in the event of fallback.

 

Below are the SQL to be executed for setting parameters based on recommendations:

At System DB SQL commands.

ALTER SYSTEM ALTER CONFIGURATION (‘nameserver.ini’, ‘SYSTEM’) SET (‘sql’, ‘max_table_count_in_statement’) = ‘0’ WITH RECONFIGURE;

ALTER SYSTEM ALTER CONFIGURATION (‘preprocessor.ini’, ‘SYSTEM’) SET (‘jobqueue’, ‘num_cores’) = ’10’ WITH RECONFIGURE;

At Tenant DB SQL commands.

ALTER SYSTEM ALTER CONFIGURATION (‘global.ini’, ‘SYSTEM’) SET (‘persistence’, ‘max_gc_parallelity’) = ’48’ WITH RECONFIGURE;

ALTER SYSTEM ALTER CONFIGURATION (‘global.ini’, ‘SYSTEM’) SET (‘resource_tracking’, ‘enable_tracking’) = ‘on’ WITH RECONFIGURE;

ALTER SYSTEM ALTER CONFIGURATION (‘global.ini’, ‘SYSTEM’) SET (‘resource_tracking’, ‘memory_tracking’) = ‘on’ WITH RECONFIGURE;

ALTER SYSTEM ALTER CONFIGURATION (‘global.ini’, ‘SYSTEM’) SET (‘resource_tracking’, ‘service_thread_sampling_monitor_enabled’) = ‘true’ WITH RECONFIGURE;

ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘SYSTEM’) SET (‘joins’, ‘single_thread_execution_for_partitioned_tables’) = ‘false’ WITH RECONFIGURE;

ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘SYSTEM’) SET (‘lobhandling’, ‘garbage_collect_interval_s’) = ‘43200’ WITH RECONFIGURE;

ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘SYSTEM’) SET (‘memorymanager’, ‘huge_alignment_cache_target’) = ‘10240’ WITH RECONFIGURE;

ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘SYSTEM’) SET (‘memorymanager’, ‘huge_alignment_gc’) = ‘false’ WITH RECONFIGURE;

ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘SYSTEM’) SET (‘transaction’, ‘aggressive_gc_interval’) = ‘300’ WITH RECONFIGURE;

 

Summary and Conclusions

This approach provides an easy way to review and set the HANA parameters based on updated SAP recommendations from time to time.

There are some other methods such as review EWA report. However, there could be situation where different value or not up to date recommendations provided. EWA report generated from customers solution manager system might not have the latest information on the parameter value recommendations due to outdated version of solution manager system or ST-SER Release installed, see note 2749491.

This is useful in project implementation, migration, and on-going support for live systems.

Hope you like this. If you have some other idea feel free to share you thought as well. =)

 

Assigned tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michael Cocquerel
      Michael Cocquerel

      the recommendations for HANA parameters are changing regularly (229 version of note 2600030 - Parameter Recommendations in SAP HANA Environments )
      Is there any possibility to automatize parameter check with always up-to-date recommendation and being automatically alerted in case of deviation.

      Author's profile photo Yi Heng Lee
      Yi Heng Lee
      Blog Post Author

      Hi Michael,

       

      That is a great question.

      Unfortunately, I have not come to a way to fully automate the process you mentioned yet.

      The note 2600030 revisions could be not only updated parameter recommendations, it could also contain updated content i.e. new information, or corrections of content from previous version etc.

      I could however see there is possibility to do the below:

      1) Set note 2600030 as favorite to received notification (launchpad or email) when there's update - see note 2171560 on this.

      2) Obtain the SQL script collections from note 1969700, which should be updated together with note 2600030.

      3) Create OS script to run hdbsql command using updated SQL script from (2) (example below) and have it ouput to a file i.e. in csv format. This can be shcedule in cron for weekly/monthly.

      #!/bin/sh
      ######################################################################
      # Script : HANA_Configuration_Parameters.txt #
      ######################################################################
      #
      hdbsql -i <Instance_Num> -d <SystemDB/Tenant_SID> -U <hdbuser_key> -I /<filepath>/HANA_Configuration_Parameters.txt -o /<filepath>/<to_save>.csv

       

      Hope this helps. Thanks.

       

      Author's profile photo Michael Cocquerel
      Michael Cocquerel

      is there any plan to integrate the parameter check in the hanachecker python utility (see https://github.com/chriselswede/hanachecker ) ?

      Author's profile photo Yi Heng Lee
      Yi Heng Lee
      Blog Post Author

      Hi Michael,

       

      I understood hanachecker will also relies on updated SQL scripts zip file from note 1969700 which has be be downloaded manually.

       

       

      Thanks.