How to reduce database size by 30% in HANA

I didn't write enough blogs last year, and felt like I abandoned. Lately a few people have kindly commented that they enjoyed reading my content, which is being nicer to me than I deserve. So here's a little gift to the year off.

This script is only useful if you have a HANA system which was installed with an older revision (SPS01-07) and you have upgraded it a bunch of times and it's now on a newer release (SPS08-10).

In that scenario, it's possibly the most useful thing you will see all year for a HANA devop. In a productive HANA system we saw disk footprint reduction from 2.9TB to 1.89TB and in-memory footprint reduction by over 100GB. It will also substantially decrease startup time, decrease backup time, and increase performance.

What happens is that HANA chooses the compression type of a column store object when it creates it, and only occasionally re-evaluates the compression type. In older databases that have a lot of data loaded since the initial installation, it can mean that the compression is suboptimal. In addition, objects can be fragmented and use more disk space than is really required.

This script takes care of all that and cleans up the system. It takes some time to run (18h in our case).

A few caveats (these are general best practices, but I have to point them out)!

• Run this script in a QA system before production, for test purposes and so you know how long it will take
• Run it at a quiet time when data loads are not running
• Ensure you have a full backup
• Use this script at your own risk, like any DDL statement it could cause issues
• Do not restart HANA during this operation
• Complete a full backup after the script, and restart HANA to reclaim memory

Thanks toLloyd Palfrey, who actually wrote it.

-- -----------------------------------------------------------------

--  HANA Reorg Script - 2015

-- -----------------------------------------------------------------

--  HOW TO RUN:

--  After creating the procedure you can run it with this statement:

--  call "_SYS_BIC"."dba_reorg"('INSERT_SCHEMA_NAME_HERE');

-- -----------------------------------------------------------------

CREATE PROCEDURE "_SYS_BIC"."dba_reorg"(IN pi_schema VARCHAR(60)) LANGUAGE SQLSCRIPT AS

CURSOR c_cursor1 FOR

SELECT TABLE_NAME

FROM M_CS_TABLES

WHERE SCHEMA_NAME = :pi_schema;

-- Recompress Tables

BEGIN

   FOR cur_tablename AS c_cursor1() DO

   EXEC 'UPDATE "' || :pi_schema || '"."' || cur_tablename.TABLE_NAME || '" WITH PARAMETERS ('OPTIMIZE_COMPRESSION'='FORCE')';

END FOR;

END;

-- End of stored procedure

-- Reorg Rowstore

EXEC 'ALTER SYSTEM RECLAIM DATA SPACE';

-- Trigger Rowstore GC

EXEC 'ALTER SYSTEM RECLAIM VERSION SPACE';

-- Create Savepoint

EXEC 'ALTER SYSTEM SAVEPOINT';

-- Reclaim LOG space

EXEC 'ALTER SYSTEM RECLAIM LOG SPACE';

-- Reclaim DATA space

EXEC 'ALTER SYSTEM RECLAIM DATAVOLUME 110 DEFRAGMENT';