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:
- 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.
- A fresh installation of
S/4 HANA 2021+ is performed. The DDIC load unit is applied
directly during installation.
- 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)
. )
.)