Row store fragmentation - impact on memory

[andrey_ryzhkov]

Hi,

we have run an SQL script "HANA_Memory_TopConsumers_1.00.85+" in our HANA SPS12 system and found that the biggest memory consumer is "Row store fragmentation" with more than 500 Gb occupied. Also there is a corresponding recommendation in alerts (Row store reorganization recommended for host <....>. Free space: 74.25%, allocated size: 782528.00MB, free size: 581067.73MB. Implement SAP Note 1813245.)

I've read the note 1813245 and don't understand exactly will be row store reorganization useful in our case or not. Tell me please how to find out how much memory (RAM, not disk) is occupied by real data of row store, and how much - by empty fragments? How much memory can be released after the reorganization? And how risky is reorganization itself (note 1813245 says that the data may be inconsistent after reorganization).

Thanks in advance,

Andrey.

[jgleichmann]

Hi Andrey,

just check out my blog "HANA memory usage details: Part 1 Shared Memory" (Part 1: Shared memory).

use statements like:

select 
CATEGORY,ALLOCATED_SIZE,USED_SIZE,FREE_SIZE 
from "SYS"."M_SHARED_MEMORY" 
where Port=30003 
order by used_size desc

or

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;

Your Row Store is really so big?

allocated size: 782528.00MB

free size: 581067.73M

real size about 200GB

With a row reorg and here I definitely recommend a offline reorg (will take a while with this size), you can save a lot of space in the data volumes and in the memory areas. This will result in a shorter startup of the system. May be you also should think about reclaiming space of the data volume with:

ALTER SYSTEM RECLAIM DATAVOLUME 120 DEFRAGMENT

Regards,

Jens

[andrey_ryzhkov]

Jens,

Many thanks for your answer and useful blog!

Tell me please, did I understand correctly that our shared memory free size (almost 600 GB) is a constant waste of memory that is not shared for any productive use?

[jgleichmann]

Hi Andrey,

yes, it is more than this, because the operating system has to manage the memory. This means a paging table entries a needed to adress this memory. This memory can be reused, but in this size it is really wasted.

But to clearly answer your question - yes, a reorg will free the sparse segments for other usage (e.g. column store tables). Currently the wasted memory can only be used for the row store.

Which revision do you currently use? There are some bugs in a few revisions which affecting the row store growth.

Regards,

Jens

[andrey_ryzhkov]

Anybody?