Row store fragmentation - impact on memory

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jan 23, 2017 at 12:19 PM

    Anybody?

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 29, 2017 at 07:21 PM

    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


    Add comment
    10|10000 characters needed characters exceeded

    • 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

Skip to Content