[HANA] SAP Notes 2600030 - Parameter Recommendations in SAP HANA Environments

Symptom

You are interested in parameter recommendations for SAP HANA environments.

Environment

SAP HANA

Cause

Inadequate parameter settings can be responsible for various severe issues:

Issue	SAP Note
High memory consumption, OOM	1999997
High CPU consumption	2100040
Bad performance	2000000
Crashes	2177064
Terminations	2399990
Wrong results	2222121
Corruptions	2116157

Consequently it is of high importance that parameters for SAP HANA and other involved system components are set according to the best practices. This SAP Note provides an overview of current recommendations in order to bypass known issues.

For a general discussion about SAP HANA parameters see SAP Note 2186744. A reference for important SAP HANA parameters is available in the SAP HANA Configuration Parameter Reference.

The SAP HANA parameter recommendations mentioned below are also reflected in the SAP HANA parameter check command SQL: "HANA_Configuration_Parameters" available via SAP Note 1969700. In addition the parameter check command will also report parameters that are currently set in the system although no general recommendation exists. In this case you should double-check if the setting is really intended or required. If not (e.g. because it is a relic of a workaround implemented on an older SAP HANA Revision), these settings should be removed.

Change log:

Date	Change
2019/04/10	qo_small_enough_exact_estimation and qo_small_enough_rough_estimation recommendations also valid for 2.00.037 (SAP Note 2756967)
2019/03/19	balance_by_execution* parameter recommendations for ERP and >= SAP HANA 1.00.70 removed (SAP Note 1899817) as they aren't required for common single node systems
2019/03/15	rsdb/tbi_buffer_area_MB >= 1000 and rsdb/tbi_dir_entries >= 20000 (SAP Note 2103827)
2019/03/07	max_num_recompile_threads = 1 for 2.00.020 - 2.00.023 (SAP Note 2664814)
2019/03/05	Lower limit of 30 GB for statement_memory_limit in SuccessFactors environments
2019/02/27	qo_small_enough_exact_estimation = 0.005 for 2.00.035 - 2.00.036 (SAP Note 2756967)
2019/02/18	qo_small_enough_exact_estimation = 0.000005 for 2.00.035 - 2.00.036 (SAP Note 2756967)
2019/02/11	optimize_convex_hull_fems_filter_threshold = -1 for 2.00.036 and BW (SAP Note 2751390)
2019/02/08	qo_small_enough_rough_estimation = 0.0000005 for 2.00.035 - 2.00.036 (SAP Note 2756967)
2019/01/07	rsdb/supports_fda_prot = 0, dbs/hdb/supports_fda_prot = 0 for 2.00.012.03 in addition to 1.00.122.14 (SAP Note 2580435)
2018/11/22	enable_sharing_allocator_for_implict = true for SAP HANA 1.0 >= 1.00.122.18 (SAP Note 2669798)
2018/11/15	virtual_vids_bitvector_threshold = 1000000000 for SAP HANA 2.00.024.01 - 2.00.024.02 and 2.00.030 (SAP Note 2642704)
2018/11/14	general rsdb/prefer_join_with_fda = 0 and dbs/hdb/prefer_join_with_fda = 0 recommendations only for SAP HANA <= 2.0 SPS 01 (SAP Note 2399993)
2018/10/17	logshipping_max_retention_size <= 80 % of log volume size (SAP Note 2526877)
2018/10/11	garbage_collect_daily_schedule_s = 3600 for SAP HANA 2.00.000 - 2.00.024.06 and 2.00.030 - 2.00.033 (SAP Note 1999930)
2018/10/10	rsdb/max_blocking_factor = 50
2018/10/01	global_auditing_state = true only in case of existing auditing policies (SAP Note 2159014)
2018/09/27	Sufficiently high value for nproc (SAP Notes 2470850, 2620175)
2018/09/26	max_gc_parallelity = MIN(50 % of CPU threads, 72) now also for SAP HANA 2.0
2018/09/24	Reference to SAP HANA Server Installation and Update Guide with various OS parameter recommendations added
2018/09/13	Sufficiently high value for /proc/sys/fs/aio-max-nr (SAP Note 1868829)
2018/09/13	Sufficiently high value for /proc/sys/vm/max_map_count (SAP Note 1980196)
2018/09/11	tablereload = info setting no longer required with >= 1.00.122.20, >= 2.00.024.04 and >= 2.00.032 because it became default (SAP Note 2222217)
2018/08/29	kernel.shmmni, kernel.shmmax, kernel.shmall (SAP Note 1999997)
2018/07/28	unused_retention_period = 0 or >= 3600 (SAP Note 2127458)
2018/07/17	hex_enabled = false for 2.00.020 - 2.00.024.03 and 2.00.030 - 2.00.031 (SAP Note 2663190)
2018/07/09	use_cfl_for_esx_expression = false for 2.00.020 - 2.00.024.03 and 2.00.030 - 2.00.031 (SAP Note 2660294)
2018/06/21	global.ini -> [resource_tracking] -> service_thread_sampling_monitor_enabled = true for >= 1.00.122.14, >= 2.00.010 (SAP Note 2114710)
2018/06/12	use_helper_threads_for_flush = false for 1.00.122.16 - 1.00.122.17 and 2.00.024.00 - 2.00.024.03 with XFS (SAP Note 2655238)
2018/06/05	multistore_feature_toggle = (multistore_operator,column,update,false) for 2.00.030 to 2.00.031
2018/06/02	internal_caching_for_main = false for 1.00.122.06 to 1.00.122.16
2018/05/30	planviz_enable = false for <= 1.00.122.17, <= 2.00.024.02, 2.00.030 in production systems (SAP Note 2119087)
2018/05/14	non_trans_cch_block_size between 16 MB and 128 MB (SAP Note 1999998)
2018/05/14	gc_cleanup_retention_sec for SAP HANA <= 1.00.122.10 and <= 2.00.012.00 (SAP Note 2474345)
2018/05/10	balance_by_* parameters for SAP HANA >= 1.00.120, scale-out adjusted (SAP Note 1958216)
2018/05/07	enable_sharing_allocator_for_implicit = false for 1.00.122.16 (SAP Note 2628153)
2018/05/07	planviz_enable = false for <= 1.00.122.16, <= 2.00.024.01, 2.00.030 in production systems (SAP Note 2637828)
2018/04/13	internal_caching_for_main = false for 1.00.122.06 to 1.00.122.99
2018/04/10	Linux stack limit needs to be kept on 8 MB (SAP Note 2488924)
2018/04/10	ABAP restart required for dbs/hdb/prefer_join_with_fda and dbs/hdb/supports_fda_prot
2018/04/09	dbs/hdb/prefer_join_with_fda = 0
2018/03/24	savepoint_pre_critical_flush_retry_threshold adjustments also required for 2.00.012.03 - 2.00.012.04 and 2.00.023 - 2.00.023
2018/03/10	rsdb/supports_fda_prot = 0, dbs/hdb/supports_fda_prot = 0 for 1.00.122.14 (SAP Note 2580435)
2018/03/09	max_concurrency (apart from specific MDC and MCOS scenarios) and max_concurrency_hint no longer need to be set explicitly starting with SAP HANA 2.00.030
2018/03/09	dbs/hdb_stmt_cache_size reduction dependent on number of ABAP work processes
2018/02/19	aggressive_gc_interval = 300 (<= 1.00 SPS 12)

 

Resolution

In this section you can find parameter recommendations for different components in SAP HANA environments:

• SAP HANA parameters
• SAP ABAP parameters
• Operating system settings

1. SAP HANA parameter recommendations

The following table lists SAP HANA parameter recommendations. Be aware about the following details:

• This SAP Note lists all parameters where SAP generally recommends a deviation from the default values. On top of it there can be individual other parameter changes that are required for specific reasons in a system (e.g. administrative settings or workarounds for less common issues).
• The suggested settings are often SAP HANA Revision specific (e.g. because the default has improved or because a workaround is no longer required with newer Revisions). The table is sorted in a way that the recommendations relevant for the most recent SAP HANA Revisions appear at the top.
• Only recommendations for SAP HANA 1.0 SPS 10 and higher are listed.
• The parameter check must not be used for SAP HANA Express installations. SAP HANA Express uses specific minimum settings that aren't in line with recommendations for normal SAP HANA installations. Furthermore there is no official SAP support for SAP HANA Express installations (SAP Note 2373555).
• Be aware that special rules apply for parameter settings in in system databases of multitenant environments (SAP Note 2101244)
• Configuration for the system database: As the nameserver takes over the role of the indexserver in system databases, recommendations for indexserver.ini have to be maintained in nameserver.ini. The recommendations below always cover the configuration for a single instance or MDC tenant and so you have to do the indexserver.ini -> nameserver.ini mapping in case of parameter settings for a system database. SQL: "HANA_Configuration_Parameters" (SAP Note 1969700) properly takes care for it when it is run in a system database.
• Default configuration for all tenants: Parameters maintained in the system database with layer SYSTEM are considered as default parameters for all tenants. SQL: "HANA_Configuration_Parameters" (SAP Note 1969700) doesn't consider this option because when it is run in the system database it isn't aware about the individual tenant scenarios that impact the parameter recommendations. Instead it is usually better to configure all parameter recommendations directly and individually in all tenants.
• Before checking the parameters of a tenant in a multitenant environment (SAP Note 2101244) you should check the parameters configured in the system DB. Otherwise critical settings performed in the system DB and inherited as default settings to the tenant may not be recognized by the tenant parameter check.
• In MCOS scenarios or multitenant environments (SAP Note 2101244) with multiple relevant tenants you have to distribute the available CPU and memory resources to the different databases at first, then you can calculate dependent parameters like max_concurrency or statement_memory_limit as fractions of these reduced numbers. See SAP Note 2222250 ("What are special considerations for MDC, MCOS and MCOD environments?") for more information. 
• In general the negative side-effects of recommended settings are not significant or - in some cases - lower than the risk / issue in case the recommendation is not implemented.

• Additional restrictions are:

  Restriction	SAP Note	Details
  ABAP	System with any kind of ABAP functionality
  AUD	2159014	Existence of auditing policies
  BPC	System with BPC functionality
  BW	BW system
  CRM	CRM system
  ERP	ERP system (e.g. SoH, S/4HANA)
  EWM	EWM system
  FRA	Fraud Management
  IQ	System with Sybase IQ remote sources
  LCACHE	2593571	Integrated liveCache
  MDCSYS	2101244	System database in multitenant system
  MDCTEN	2101244	Tenant database in multitenant system
  MULTI	Scale-out system (one master, at least one slave)
  PORTAL	Enterprise Portal
  PROD	Production system (not test, development, ...)
  S4	S/4HANA system
  SCM	SCM system
  SDA	2180119	System using smart data access (SDA)
  SERIES	Existence of series tables
  SF	SuccessFactors
  SINGLE	Single node systems (one master, no slave)
  SRM	SRM system
  SYSREP	1999880	SAP HANA databases with activated system replication
  XFS	1999930	XFS file system

• Column "P" contains the priority for implementing the recommendations:

  Priority	Details
  1	Very high impact or risk if parameter is not implemented
  2	High impact or risk if parameter is not implemented
  3	Medium impact or risk if parameter is not implemented
  4	Low impact or risk if parameter is not implemented

• Column "R" describes if a restart of SAP HANA or a different component is required so that the settings take effect:

  Restart	Details
  A	Restart of ABAP application servers required
  H	Restart of SAP HANA required
  N	No restart required
  S	Restart of secondary system replication site required (if applicable)

• Column "A" lists the area / problem scenario that is covered with the parameter setting:

  Area	Details
  C	Crash
  M	Monitoring
  N	Network, communication
  I	Inconsistency, corruption, wrong results
  P	Performance
  R	Redistribution, table placement
  S	Security
  T	Termination
  W	Workload, resources

List of SAP HANA parameter recommendations:

Valid for	Parameter	Recommendation	SAP Note	P	R	A	Details
>= 2.00.030	global.ini -> [execution] -> max_concurrency	100 % of available CPU threads, at least 4, if possible multiple of CPU threads per NUMA node	2222250	1	N	W	Overall number of CPU threads that can be used by parallelized requests (i.e. JobWorkers) Starting with SAP HANA 2.0 SPS 03 the parameter max_concurrency_dyn_min_pct takes care for an appropriate concurrency reduction, so max_concurrency only has to be set explicitly if not all CPUs should be used by the database (e.g. in context of MDC or MCOS)
all Revisions	global.ini -> [expensive_statement] -> enable	true	2180165	3	N	M	Activates expensive statements trace to capture particularly long running SQL statements proactively, default threshold is 1 s and can be configured with the threshold_duration parameter
all Revisions	global.ini -> [memoryobjects] -> unused_retention_period	0 or >= 3600	2127458	2	N	P	Makes sure that unloads don't happen with a high frequency due to a small retention period setting
all Revisions	global.ini -> [persistence] -> non_trans_cch_block_size	16777216 - 134217728	1999998	2	N	P	Reduces ConsistentChangeLock allocation times during table optimization writes
all Revisions	indexserver.ini -> [parallel] -> tables_preloaded_in_parallel	5 - 10 % of available CPU threads, at least 5	2222250	3	H	P	Maximum number of tables that are loaded in parallel during column store load, can improve load throughput during restart
MDCTEN all Revisions	global.ini -> [memorymanager] -> allocationlimit	available memory for tenant	1999997	2	N	W	Set to the available memory for the tenant (MB), in case of only 1 tenant: 100 %, otherwise a lower percentage of the memory based on the intended distribution of memory to tenants
SYSREP all Revisions	global.ini -> [system_replication] -> logshipping_max_retention_size	0 - 80 % of log volume size	2526877	3	N	P	Reduce risk of log volume full situations due to a high amount of retained log file for preserving system replication consistency between primary and secondary site Strictly speaking only relevant for logreplay operation modes, but generally recommended for system replication environments to be prepared when delta_datashipping is switched to logreplay operation modes
>= 1.00.122.14 >= 2.00.010	global.ini -> [resource_tracking] -> service_thread_sampling_monitor_enabled	true	2114710	2	N	M	Activate thread sampling for all SAP HANA services (default: only for indexserver) to get a complete picture of historic database activities
MULTI >= 1.00.120	indexserver.ini -> [table_redist] -> balance_by_execution_count indexserver.ini -> [table_redist] -> balance_by_execution_time indexserver.ini -> [table_redist] -> balance_by_memuse indexserver.ini -> [table_ redist] -> balance_by_part_id indexserver.ini -> [table_redist] -> balance_by_partnum indexserver.ini -> [table_redist] -> balance_by_rows indexserver.ini -> [table_redist] -> balance_by_table_classification indexserver.ini -> [table_redist] -> balance_by_table_size_hosted indexserver.ini -> [table_redist] -> balance_by_table_subclassification	true true false false false false false true false	1958216	3	N	R	Optimizes table redistribution
BW >= 1.00.120	global.ini -> [table_placement] -> max_partitions global.ini -> [table_placement] -> max_partitions_limited_by_locations	Allocation limit > 2048 GB: 4 (0 slave nodes) 8 (1 slave node) 12 (2 or more slave nodes) false	1908075 2334091	2	N	R	Controls maximum number of table partitions in context of table redistribution
1.00.122.18 - 1.00.122.99	global.ini -> [memorymanager] -> enable_sharing_allocator_for_implicit	true	2669798	3	N	M	Allocator sharing for implicit memory booking
>= 1.00.100	global.ini -> [communication] -> tcp_backlog	2048	2382421	2	H	N	Optimizes configuration to handle communication spikes between SAP HANA services and nodes
>= 1.00.100	global.ini -> [table_placement] -> max_rows_per_partition	1500000000	1908075 2334091	2	N	R	Controls maximum number of table rows in context of table redistribution
>= 1.00.102.03	global.ini -> [execution] -> default_statement_concurrency_limit	MIN(30 %, 16) - 50 % of available CPU threads, at least 4, if possible multiple of CPU threads per NUMA node	2222250	1	N	W	Overall number of CPU threads that can be used by a single database request, optimal setting is individual, rough rule of thumb: OLTP: Use a value closer towards the lower limit (e.g. number of logical cores on one or two sockets) OLAP: Use a value closer towards the upper limit Example: 288 CPU threads, 16 NUMA nodes, 18 CPU threads per NUMA node, OLAP -> default_statement_concurrency_limit = 144
AUD >= 1.00.93	global.ini -> [auditing configuration] -> global_auditing_state	true	2159014	3	N	S	Activation of auditing features, imposes general overhead in terms of memory (Pool/Auditing) and CPU, so it should be only activated if audit policies are present
>= 1.00.80	global.ini -> [memorymanager] -> statement_memory_limit	MIN(10 %, 100 GB) - MIN(30 %, 500 GB) of SAP HANA global allocation limit SuccessFactors: MIN(10 %, 30 GB) - MIN(30 %, 500 GB) of SAP HANA global allocation limit	1999997 2222250	1	N	W	Restriction of memory that can be allocated by a single database request, optimal setting is individual, rough rule of thumb: OLTP: Use a value closer towards the lower limit (e.g. 10 %) OLAP: Use a value closer towards the upper limit (e.g. 30 %) Example: 1000 GB GAL, OLTP -> statement_memory_limit = 100 GB
>= 1.00.80	global.ini -> [resource_tracking] -> enable_tracking	on	1999997	1	N	M	Prerequisite for important features like the statement memory limit
>= 1.00.80	global.ini -> [resource_tracking] -> memory_tracking	on	1999997	1	N	M	Prerequisite for important features like the statement memory limit
MULTI >= 1.00.70	global.ini -> [internal_hostname_resolution] -> <ip_address> global.ini -> [communication] -> listeninterface	<host_name> .internal	2183363	2	S	S	Restricts listener to internally defined ports, mappings of IP addresses to host names need to be specified for all SAP HANA nodes
all Revisions	max_gc_parallelity	MIN(50 % of CPU threads, 72)	2222250	2	H	P	Restricts the maximum number of concurrently active persistence garbage collector threads, higher numbers can result in system-wide JobWorker bottlenecks Example: 288 CPU threads -> max_gc_parallelity = 72
BW 2.00.036	indexserver.ini -> [calcengine] -> optimize_convex_hull_fems_filter_threshold	-1	2751390	1	N	P	Reduces risk of performance regressions in context of BW and FEMS
2.00.035 - 2.00.037	indexserver.ini -> [search] -> qo_small_enough_exact_estimation indexserver.ini -> [search] -> qo_small_enough_rough_estimation	0.005 0.0000005	2756967	1	N	P	Reduces risk that selective index isn't considered
2.00.000 - 2.00.024.06 2.00.030 - 2.00.033	indexserver.ini -> [lobhandling] -> garbage_collect_daily_schedule_s	3600	2709767	1	N	P	In context of packed LOBs the column store garbage collector scans all LOBs on disk whenever it is running, and per default it is scheduled all 15 minutes. With this parameter setting it will run only once a day, at 01:00 (3600 seconds after midnight).
2.00.030 - 2.00.031	indexserver.ini -> [sql] -> multistore_feature_toggle	(multistore_operator,column,update,false)	2351294	2	N	P	Optimizes performance of bulk updates on views
2.00.020 - 2.00.024.03 2.00.030 - 2.00.031	indexserver.ini -> [sql] -> hex_enabled	false	2663190	2	N	C	Disables SAP HANA execution engine (HEX, SAP Note 2570371) to eliminate risk of crashes
2.00.020 - 2.00.024.03 2.00.030 - 2.00.031	indexserver.ini -> [sql] -> use_cfl_for_esx_expression	false	2660294	3	N	C	Eliminates risk of crashes in context of ESX (SAP Note 2599949) and parallel hash join
<= 1.00.122.19 <= 2.00.024.03 <= 2.00.031	indexserver.ini -> [trace] -> tablereload	info	2222217	4	N	M	Activates light-weight trace entries to understand column store load activities during restart
2.00.024.01 - 2.00.024.02 2.00.030	indexserver.ini -> [joins] -> virtual_vids_bitvector_threshold	1000000000	2642704	2	N	C	Eliminates the risk of a SAP HANA indexserver crash in context of join engine and memory deallocations
<= 2.00.029	global.ini -> [execution] -> max_concurrency	33 - 100 % of available CPU threads, at least 4, if possible multiple of CPU threads per NUMA node	2222250	1	N	W	Overall number of CPU threads that can be used by parallelized requests (i.e. JobWorkers), values below 50 % only required in rare cases, optimal setting is individual, rough rule of thumb: OLTP: Use a value closer towards the lower limit (e.g. 50 %) OLAP: Use a value closer towards the upper limit (e.g. 100 %) Example: 192 CPU threads, 16 NUMA nodes, 12 CPU threads per NUMA node, OLTP -> max_concurrency = 96
<= 2.00.029	global.ini -> [execution] -> max_concurrency_hint	MIN(10 %, 8) - 50 % of available CPU threads, at least 4, if possible multiple of CPU threads per NUMA node	2222250	1	N	W	Maximum number of CPU threads that can be used by a single parallelized operation, optimal setting is individual, rough rule of thumb: OLTP: Use a value closer towards the lower limit (e.g. 20 %) OLAP: Use a value closer towards the upper limit (e.g. 50 %) Example: 144 CPU threads, 12 NUMA nodes, 12 CPU threads per NUMA node, OLAP -> max_concurrency_hint = 96
PROD <= 1.00.122.17 2.00.000 - 2.00.024.02 2.00.030	indexserver.ini -> [performance_analyzer] -> planviz_enable	false	2119087	3	N	C	Eliminates risks of crashes or memory leaks in context of PlanViz
1.00.122.16 - 1.00.122.17 2.00.024.00 - 2.00.024.03	global.ini -> [persistence] -> use_helper_threads_for_flush	false	2655238	1	N	C	Deactivates parallel flush threads to avoid massive XFS contention and risk of crashes Attention: Setting is deactivated after restart (SAP Note 2655468) and needs to be activated again (SAP HANA <= 1.00.122.18, <= 2.00.024.03, <= 2.00.031).