Thursday, 28 May 2020

SAP HANA Row Store Reorg


1.       Execute below SQL to check current status – 
SELECT HOST, PORT, CASE WHEN (((SUM(FREE_SIZE) / SUM(ALLOCATED_SIZE)) > 0.30) AND SUM(ALLOCATED_SIZE) > TO_DECIMAL(10)*1024*1024*1024) THEN 'TRUE' ELSE 'FALSE' END "Row store Reorganization Recommended", TO_DECIMAL( SUM(FREE_SIZE)*100 / SUM(ALLOCATED_SIZE), 10,2) "Free Space Ratio in %",TO_DECIMAL( SUM(ALLOCATED_SIZE)/1048576, 10, 2) "Allocated Size in MB",TO_DECIMAL( SUM(FREE_SIZE)/1048576, 10, 2) "Free Size in MB" FROM M_RS_MEMORY WHERE ( CATEGORY = 'TABLE' ) GROUP BY HOST, PORT 

 If the result of "Reorganization Recommended" is "TRUE", then row store memory can be reclaimed after row store reorganization. 

 

2.       Catalog integrity check 
o    The following procedure calls should run successfully and return empty result sets. 
                              CALL CHECK_CATALOG ('CHECK_OBJECT_REFERENTIAL_INTEGRITY','','',''); 
                              CALL CHECK_TABLE_CONSISTENCY('CHECK_ROW_TABLES', '', '') 
o    If any of the procedure calls return non-empty result set, DO NOT proceed Row Store Reorganization and refer to SAP Note 1977584 for further info. 
 


 


3.       Do below steps for reorg- 

Make a complete database backup. 
Set up configuration parameter in indexserver.ini via HANA Studio. 
[row_engine]
 
page_compaction_enable = true
 
page_compaction_max_pages = 2097152
 
Restart the database. Startup will take longer than normal startup when row store reorganization runs. 





4.       Execute below SQL again, now all entries will be in false status means row store reorg is successful  
SELECT HOST, PORT, CASE WHEN (((SUM(FREE_SIZE) / SUM(ALLOCATED_SIZE)) > 0.30) AND SUM(ALLOCATED_SIZE) > TO_DECIMAL(10)*1024*1024*1024) THEN 'TRUE' ELSE 'FALSE' END "Row store Reorganization Recommended", TO_DECIMAL( SUM(FREE_SIZE)*100 / SUM(ALLOCATED_SIZE), 10,2) "Free Space Ratio in %",TO_DECIMAL( SUM(ALLOCATED_SIZE)/1048576, 10, 2) "Allocated Size in MB",TO_DECIMAL( SUM(FREE_SIZE)/1048576, 10, 2) "Free Size in MB" FROM M_RS_MEMORY WHERE ( CATEGORY = 'TABLE' ) GROUP BY HOST, PORT 

5.       Remove parameters which you set in Step3