How-to Review and Set SAP HANA Parameters - UPDATED Recommendations

 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

 

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 Results

HANA Cockpit Database Configuration

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;