2894395 - Delta Merge is not running in HANA

Delta Merge is not running in HANA.
There are lot's of alerts related to delta merge.

1. Alerts
---
[Schema Name].[Table Name] partition 1 on xxx:3XX03 has a delta storage size of 3190MB. This exceeds the threshold value and may indicate a problem with delta merge processing.
...
[Schema Name].[Table Name] partition 0 on xxx:3XX03 has a delta storage size of 2660MB. This exceeds the threshold value and may indicate a problem with delta merge processing.
...
---

Referred the note 2043152 Alert "Size of delta storage exceeds the threshold value and may indicate a problem with delta merge processing", But there are so much main memory has been kept occupying by tables which are already do the delta merge operation.

1. Check the memory status in HANA

1) Memory Overview
- Check allocators if the largest ones belong to the HANA delta store.
---
NAME;TOTAL_GB;DETAIL_GB;DETAIL2_GB
HANA heap memory (used); 671; 671 (spilhanab4n1); 347 (Pool/PersistenceManager/UnifiedTableContainer)

; ; ; 137 (Pool/ColumnStore/Delta/LeafNodes)
; ; ; 53 (Pool/ColumnStore/Delta/Btreeindex)
; ; ; 30 (Pool/ColumnStore/Delta/InternalNodes)
; ; ; 24 (Pool/PersistenceManager/PersistentSpace/DefaultLPA/Page)
; ; ; 10 (Pool/CSRowLocking)
---

2) Run the following query to check the size of delta storage.
- select SUM(MEMORY_SIZE_IN_DELTA) from M_CS_TABLES
---
SUM(MEMORY_SIZE_IN_DELTA)
595,528,088,149
---

3) Check which tables are occupying the memory related to delta
- select top 30 * from M_CS_TABLES order by memory_size_in_delta desc

2. Check the configuration of the system if the parameters related to mergedog are set to unfavorable values.

1) indexserver.ini
---
[mergedog]
auto_merge_decision_func = xxxx
critical_merge_decision_func = xxxx
token_per_table = xxxx
num_merge_token = xxxx

[optimize_compression]
min_hours_since_last_merge_of_part = xxxx
---

2) Check the parameters with the following script.

- HANA_Configuration_Parameters_*+.txt
---
FILE_NAME;SECTION;PARAMETER_NAME;P;DEFAULT_VALUE;CONFIGURED_VALUE;RECOMMENDED_VALUE;SAP_NOTE;CONFIG_LAYER;IMPLEMENTATION_COMMAND;UNDO_COMMAND
indexserver.ini;mergedog;auto_merge_decision_func;;((DRC*TMD > 3600*(MRC+0.0001)) or ((DMS>PAL/2000 or DMS > 1000 or DCC>100) and DRC > MRC/100) or (DMR>0.2*MRC and DMR > 0.001));DMS>5000 and (DUC<0.1 or 0.05*DRC>=DUC);-- check why set --;2057046;DATABASE;ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'DATABASE') UNSET ('mergedog', 'auto_merge_decision_func') WITH RECONFIGURE;;ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'DATABASE') SET ('mergedog', 'auto_merge_decision_func') = 'DMS>5000 and (DUC<0.1 or 0.05*DRC>=DUC)' WITH RECONFIGURE;

indexserver.ini;mergedog;critical_merge_decision_func;;UPT > 43200 and ((MMS<10000 and DMS>1000 and TMD>86400) or TMD>604800);DMS>12000 and (DUC<0.1 or 0.05*DRC>=DUC);-- check why set --;2057046;DATABASE;ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'DATABASE') UNSET ('mergedog', 'critical_merge_decision_func') WITH RECONFIGURE;;ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'DATABASE') SET ('mergedog', 'critical_merge_decision_func') = 'DMS>12000 and (DUC<0.1 or 0.05*DRC>=DUC)' WITH RECONFIGURE;

indexserver.ini;mergedog;num_merge_token;;-- HANA internal --;200;-- check why set --; ;DATABASE;ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'DATABASE') UNSET ('mergedog', 'num_merge_token') WITH RECONFIGURE;;ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'DATABASE') SET ('mergedog', 'num_merge_token') = '200' WITH RECONFIGURE;

indexserver.ini;optimize_compression;min_hours_since_last_merge_of_part;;-- HANA internal --;0;-- check why set --;2105761;DATABASE;ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'DATABASE') UNSET ('optimize_compression', 'min_hours_since_last_merge_of_part') WITH RECONFIGURE;;ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'DATABASE') SET ('optimize_compression', 'min_hours_since_last_merge_of_part') = '0' WITH RECONFIGURE;
---

Image /data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

• SAP HANA Database

HANA, Delta Merge, delta storage size, This exceeds the threshold value, alert , KBA , HAN-DB , SAP HANA Database , Problem