How does row store grows ?
Row store grows by allocating a 64MB memory segment and decrease by freeing empty segments. A segment is internally divided into fixed-size pages.
Now when anything is added in rowstore what happens is , it first looks for space in existing segment and if space is not available in existing ones in that case it take a free page and new segment is allocated.
Hypothetically , suppose [just a example to help you understand easily]
A has 98/100 [this means out of 100 internally divided pages of the segment 98 is occupied ]
when a new row store add request is of 36 pages then it is added by adding in a empty segment say B
B has 36/100 if, another request comes up for 40 it can be added in B so it will sum up to B [76/100]
We have a new request of say 26 then again we will require a new segment say C [26/100] to do the other store
What happens when we reorganize and why we reorganize?
When a large number of records are deleted it can happen that small or large part of many network segments are deleted but since HANA free ups only the empty segments the memory is still consumed.
The pages in sparse segments are moved to other segment the empty segments are then free.
Suppose after a series of transactions and we can have only 3 segments
Now A has [20/100] , B has [50/100] and C has [30/100] and a request comes for say 90 pages , since no segment is 100% free we will have issue with memory .
What happens in reorganise is that A will have something like [100/100] and rest of segment B and C will be free.
ROWSTORE REORG on HA and DR
Row store memory size of secondary site in HSR setup with logreplay or logreplay_readaccess mode may differ from that on primary site.
However no offline or online reorganization is not possible since write operations are probhibited
Alternatively you can reorganize by re registering the secondary site or perform row store reorganization at time of takeover
Best Practise would be : Perform reorg on primary and then re register replication
In case of HA, we can use full replica --force_full_replica or sr_initialize
When to perform a reorganise ?
For when and how to perform reorganise refer SAP NOTE 1813245
1. Row store reorganisation is recommended , if allocated row store table size is greater than 10GB and free page ratio is greater than 30%
2. Query to have an estimation of how much memory will be reclaimed
SELECT HOST, PORT, FREE_SIZE/1024768 "Estimated Maximum Memory Saving in MB" FROM M_RS_MEMORY WHERE CATEGORY IN ('TABLE')
How we perform a reorganise ?
From SAP HANA 2.0 SPS04 you can easily configure automatic row store reorganisation using SAP NOTE : 2789255
ONLINE SAP NOTE 1813245
From SAP HANA 2.0 SPS00 - SPS03 ,You need to perform it manually by logging as SYSTEM user and running ALTER SYSTEM RECLAIM DATA SPACE , it can be executed at runtime but tables which will be reorganised are exclusively locked thus no update can be performed on those particular tables . Due to the complexity of runtime there can still be fragmented row store memory since the tables that will be used at that point of time will not be used , so we should try to perform this activity when the least amount of tables will be used so we get a good result
OFFLINE SAP NOTE 1813245
When the restart time , the row store reorganisation is required , Since it is guaranteed that there are no update transaction at that time so we can achieve the maximum out of it. So we enable the reorganisation parameter and then perform the restart
1. On tenant DB
ALTER SYSTEM ALTER CONFIGURATION ( 'indexserver.ini' , 'SYSTEM' ) SET ( 'row_engine' , 'page_compaction_enable' ) = 'true' ;
2. ALTER SYSTEM ALTER CONFIGURATION ( 'indexserver.ini' , 'SYSTEM' ) SET ( 'row_engine' , 'page_compaction_max_pages ' ) = '2097152' ;
3. USE hdbsql to login in SYSTEMDB AND Stop the tenant DB
4. Start the tenant DB
5. Perform cdtrace and check the [RSReorg] success
Post this steps make sure that you perform following
ALTER SYSTEM ALTER CONFIGURATION ( 'indexserver.ini' , 'SYSTEM' ) UNSET ( 'row_engine' , 'page_compaction_enable' )
ALTER SYSTEM ALTER CONFIGURATION ( 'indexserver.ini' , 'SYSTEM' ) UNSET ( 'row_engine' , 'page_compaction_max_pages ' )