FAQ: SAP HANA Native Storage Extension (NSE)

1: How can I activate NSE?

NSE can be activated via DDL commands on the desired tables. There are no additional configuration steps needed.

2. I have activated NSE for a database object but want to revert my changes. How can I achieve this?

Changes to database objects can be reverted by setting the load granularity to default.

ALTER TABLE "<table_name>" DEFAULT LOADABLE;

Adding the option CASCADE allows to overwrite all modifications on the below layers:

ALTER TABLE "<table_name>" DEFAULT LOADABLE CASCADE;

 

3. Where do I find information about buffer cache usage?

Information on the buffer cache usage such as hit ratio and size can be obtained in the following locations:

• M_BUFFER_CACHE_STATISTICS
• M_BUFFER_CACHE_POOL_STATISTICS
• SQL: "HANA_NSE_BufferCache" (SAP Note 1969700)
• M_SQL_PLAN_CACHE (starting SAP HANA 2.0 SPS07, columns *_BUFFER_CACHE_IO_READ_SIZE, *_BUFFER_CACHE_PAGE_MISS_COUNT and *_BUFFER_CACHE_PAGE_HIT_COUNT)
• SQL: "HANA_SQL_SQLCache_2.00.070+" (SAP Note 1969700)

4. How can I calculate the memory consumption of NSE enabled tables?

As explained in question #1, pages of NSE tables, when loaded into memory, are loaded into the buffer cache. Some helper structures for the columns are still loaded into the main store area of HANA and reside there until the column is unloaded.
After reading an NSE table, its non-pageable area will be loaded to the main store memory, while the pageable area will be loaded into the buffer cache. If, just after this read, you check the memory consumption for the table, the value will be the sum of main store memory and the memory occupied in the buffer cache. Keeping in mind the nature of the Buffer Cache, you will understand that the allocation in the buffer cache is transient. As soon as another NSE table is read, those pages loaded into the buffer cache may be displaced in a least-recently used fashion. Or they may stay there for longer, if there is enough space in the buffer cache to accommodate both tables. Given the transient characteristic of the pages loaded into the buffer cache, for the purpose of determining memory savings brought by NSE, this memory area should not be considered for the definition of the total memory allocated by the NSE table.

SQL: "HANA_Tables_ColumnStore_TableSize_2.00.030+" (SAP Note 1969700) reports the NSE-related memory consumption in column PAGED_GB.

5. Where is the buffer cache located?

The buffer cache is stored in the heap allocator Pool/CS/BufferPage. The allocator always resides in DRAM. This also applies to systems using PMEM or Fast Restart Option.

6. How does the buffer cache behave during memory pressure?

The NSE Buffer cache is not unloaded as part of shrink operations. As a result, the memory used by the NSE cache cannot be released and be reused by other components in case the database runs out of memory.

To avoid memory issues, the buffer cache should be configured with a reasonable size depending on the size of tables located in NSE.

7. How to handle NSE advisor recommendations to move columns like $trexexternalkey$ or $AWKEY_REFDOC$GJAHR$?

$trexexternalkey$ or $AWKEY_REFDOC$GJAHR$ are internal columns (SAP Note 1986747) that refer to the primary key respectively a multi-column index/concat attribute. Indexes can be moved to NSE as well.

a) identify index name:

SELECT SCHEMA_NAME, TABLE_NAME, INDEX_NAME, INDEX_TYPE, CONSTRAINT FROM INDEXES WHERE TABLE_NAME = '<table_name>';

b) change the index load granularity

ALTER INDEX "<schema>"."<index_name>" PAGE LOADABLE;example: ALTER INDEX "PLAYGROUND"."_SYS_TREE_CS_#170484_#0_#P0" PAGE LOADABLE;

 

8. How can I get information on the impact of NSE on query performance?

Starting SAP HANA 2.0 SPS07 monitoring view M_SQL_PLAN_CACHE contains several additional columns  that provide deeper insight on NSE usage during query execution (*_BUFFER_CACHE_IO_READ_SIZE, *_BUFFER_CACHE_PAGE_MISS_COUNT and *_BUFFER_CACHE_PAGE_HIT_COUNT).

The following sql commands in SAP Note 1969700 exist to support the investigation:

• SQL: "HANA_SQL_SQLCache_2.00.070+" 
• SQL: "HANA_SQL_SQLCache_TopLists_2.00.070+" 

9. Does DDIC support NSE?

Since S/4 HANA 2021, DDIC supports load unit settings "Page Preferred" and "Page Enforced". Depending on the S/4 HANA release, these settings can change.
The current configuration can either be checked via SE11 -> "Technical Settings" -> "DB-Specific Properties".
Alternatively, you can query the details directly in DBACOCKPIT via the following SQL:

select
    t.TABLE_NAME, t.LOAD_UNIT HDB_LOAD_UNIT,
    CASE WHEN d.LOAD_UNIT = 'P' THEN 'Page Preferred'
      WHEN d.LOAD_UNIT = 'Q' THEN 'Page Enforced' END DDIC_LOAD_UNIT
FROM
    TABLES t full outer join DD09L d ON t.table_name = d.tabname
WHERE
    (t.LOAD_UNIT = 'PAGE' OR d.LOAD_UNIT IN ('P', 'Q')) AND t.SCHEMA_NAME LIKE 'SAP%';

For further details on DDIC integration, please see SAP Note 2973243.

10. When are DDIC load unit settings applied and what happens if an upgrade changes the DDIC load unit?

Whether load unit settings are applied automatically depends on the scenario:

1. When converting from anyDB to SAP S/4 HANA, the DDIC load unit is applied in the course of the migration, tables with "page preferred" and "page enforced" are created using NSE.
2. A fresh installation of S/4 HANA 2021+ is performed. The DDIC load unit is applied directly during installation.
3. A conversion from Suite on HANA to S/4 HANA or a S/4 HANA upgrade does not enforce DDIC load unit settings. In this scenario only exchange tables are created with the new settings. The load unit setting of existing tables will be preserved. Only a change to "Page Enforced" will result in a load unit change on the database. Other SAP_BASIS objects with load unit "Page Preferred" such as BALDAT, CDPOS, and EDID4 stay as is and will not be automatically changed.

11. Do I need additional disk space when using NSE?

During conversion to page loadable, the persistence structures are rewritten for optimal usage with NSE. In most cases, this does not change the total amount of disk space needed. However, in rare cases tables, partitions, and columns that are converted to NSE may see an increased disk usage up to 80 %. Some column data types (like TIMESTAMP, DATE, CHAR, VARCHAR and NVARCHAR) do not support all possible dictionary compressions if columns are stored as page loadable.

12. Is the buffer cache preloading data after a system restart just like the normal column store does?

No, the buffer cache will be empty after restart and only be populated on demand.

Note: 

Please refer to the SAP note to get full details. 

2799997 - FAQ: SAP HANA Native Storage Extension (NSE)