Wednesday, 11 October 2017

Hana DB Row Store Reorganization

How To Perform Hana DB Row Store Reorganization


Row store memory size is a lot bigger than the actual data size in row store and shows high fragmentation ratio

A row store table requires more memory to store records, the table takes a free page from existing segments. If no segment has a free page, a new segment is allocated. Deleting a large number of records may result in a number of sparse segments. In such a case, row store reorganization is very useful and can be performed for memory compaction.
  • Row store reorganization is recommended, when allocated row store size is over 10GB and free page ratio is over 30%.
  • Online Row Store Reorganization can be performed for any HANA Database revisions for SPS10 or later
  • For HANA 2.0 Database Revision 020 (2.00.020.00), online reorganization is disabled until further notice.
Row Store Reorganization can be performed either Online (or) Offline
Aspect Offline reorganization Online reorganization
Availability SAP HANA database is completely unavailable Currently reorganized table is blocked against changes (transactional object lock)
----------------------------------------------------------------
Read operations to all tables and modifications of other SAP HANA tables are still possible
Restart required Yes (reorganization happens during restart) No
Defragmentation Maximum defragmentation Significant, but not always maximum defragmentation
Memory footprint Optimal memory footprint Increased memory footprint in case of concurrent changes or open transactions
Runtime Optimal Increased in case of resource or object lock contention


Prerequisites:


HANA Database has to be upgraded prior to running row store reorganization.

Our recommendation is to run it at idle time to maximize compaction.

Row store reorganization is recommended, when allocated row store size is over 10GB and free page Ratio is over 30%. If the result of “Reorganization Recommended” is “TRUE“, then row store memory can be reclaimed after row store reorganization.

If the prerequisites are not satisfied, row store reorganization MUST not be executed. The prerequisites, queries, and monitoring views used for the queries are applicable to Rev 85.01 and Rev.91 and subject to change in future releases.

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' OR CATEGORY = 'CATALOG' ) GROUP BY HOST, PORT;

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

Estimation on maximum possible memory space that can be reclaimed:

SELECT SUM(NUM_FREE_DATA_PAGES)*16/1024 “Estimated Maximum Memory Saving in MB” FROM SYS.M_DEV_MEMORY_SEGMENT WHERE SEGMENT_TYPE = 0;

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

Check disk space where log files are located.

SELECT (SUM(NUM_FREE_DATA_PAGES)*16/1024)*1.5 "Estimated Log Volume Size in MB" FROM SYS.M_DEV_MEMORY_SEGMENT WHERE SEGMENT_TYPE = 0;

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

Online Row Store Reorganization execution steps:


Note: Be informed that online row store reorganization acquires exclusive table lock of a number of tables which are dynamically chosen at runtime of online row store reorganization. Thus, update operations on those tables cannot be done until the online row store reorganization finishes reorganization of corresponding tables.

1. Upgrade database to Rev.85.01 or later and Rev.91 or later for SPS08 and SPS09, respectively.
2. Make a complete database backup.
3. (Optional but strongly recommended) Confirm that the system is in relatively in idle state.
4. Make a new connection to the system (HANA Studio is recommended)
5. Run Row Store Consistency Checker and check whether the system is in inconsistent state. For Details, refer to SAP Note 1977584.

CALL CHECK_TABLE_CONSISTENCY('CHECK_ROW_TABLES', NULL, NULL);

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

6. If the result is not empty, DO NOT run online row store reorganization and follow the solution of
SAP Note 1977584.

7. Execute Online Row Store Reorganization command

ALTER SYSTEM RECLAIM DATA SPACE

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

8. To see the progress, check the “Job Progress” Tab in “Performance” Tab of the HANA Studio

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

9. Check the indexserver trace files after row store reorganization is done. The trace of Online Row

Store Reorganization is separately managed in indexserver_<hostname>.<port>.row_store_reorg.

<sequence>.trc

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

- Successful case (Note that the traces can be changed depending on the Revision)

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

10. Check the memory fragmentation status after reorganization. Due to its limitation stated above,you may encounter the case where reorganization is still recommended even after running online Reorganization.

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' OR CATEGORY = 'CATALOG' ) AND PORT LIKE '%03' GROUP BY
HOST, PORT;

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

11. Repeat step (5) Run Row Store Consistency Checker and check whether the system is in inconsistent state. For details, refer to SAP Note 1977584.

CALL CHECK_TABLE_CONSISTENCY('CHECK_ROW_TABLES', NULL, NULL);

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

If the result is not empty, open a SAP incident on component HAN-DB in order to get further

Assistance from SAP side. It is recommended to export the result of the consistency check and

Upload it.

Result of this activity is:

We could see the allocated row store memory before reorganization around 32 GB now it is 24 GB, around 8 GB have been reclaimed due to this activity.

After row store reorganization

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio

Before row store reorganization

SAP HANA Tutorials and Materials, SAP HANA Certifications, SAP HANA Guides, SAP HANA Learning, SAP HANA Studio