SAPFree generatorPDF, Excel, Word exportSAP HANAAll SAP HANA and Basis tools

SAP HANA Load and Unload Table from Memory: SQL Generator

This generator writes the SAP HANA statements to see whether a column table is in memory and to load, unload, preload, reprioritise or page-enable it. Enter a schema and table, copy the block you need.

Fill in

Schema

Table

Unload priority (0-9)

Partition ID for NSE (optional)

Generated code

Download:

1. Check load status and memory

SELECT SCHEMA_NAME, TABLE_NAME, PART_ID, LOADED,
       RECORD_COUNT, ROUND(MEMORY_SIZE_IN_TOTAL / 1024 / 1024, 1) AS MEMORY_MB
  FROM M_CS_TABLES
 WHERE SCHEMA_NAME = 'SAPHANADB' AND TABLE_NAME = 'CDPOS'
 ORDER BY PART_ID;

SELECT SCHEMA_NAME, TABLE_NAME, UNLOAD_PRIORITY, LOAD_UNIT
  FROM TABLES
 WHERE SCHEMA_NAME = 'SAPHANADB' AND TABLE_NAME = 'CDPOS';

2. Load the whole table into memory

LOAD "SAPHANADB"."CDPOS" ALL;

3. Unload the table from memory

UNLOAD "SAPHANADB"."CDPOS";

4. Preload after restart (or switch it off)

ALTER TABLE "SAPHANADB"."CDPOS" PRELOAD ALL;
-- switch off:
ALTER TABLE "SAPHANADB"."CDPOS" PRELOAD NONE;

5. Change unload priority

-- 0 = never unloaded under memory pressure, 9 = unloaded first, 5 = usual default for column tables
ALTER TABLE "SAPHANADB"."CDPOS" UNLOAD PRIORITY 5;

6. Native Storage Extension: page loadable (HANA 2.0 SPS04 or later)

-- whole table, including columns and partitions
ALTER TABLE "SAPHANADB"."CDPOS" PAGE LOADABLE CASCADE;
-- one partition only
ALTER TABLE "SAPHANADB"."CDPOS" ALTER PARTITION 1 PAGE LOADABLE;
-- back to fully in memory
ALTER TABLE "SAPHANADB"."CDPOS" COLUMN LOADABLE CASCADE;

7. Unload history

SELECT TOP 50 UNLOAD_TIME, HOST, PORT, SCHEMA_NAME, TABLE_NAME, PART_ID, COLUMN_NAME, REASON
  FROM M_CS_UNLOADS
 WHERE SCHEMA_NAME = 'SAPHANADB' AND TABLE_NAME = 'CDPOS'
 ORDER BY UNLOAD_TIME DESC;

Run in the tenant with a user that owns the table or has ALTER and the needed system privileges; changes to SAP standard tables should follow SAP recommendations (NSE advisor, S/4HANA NSE guidance). Before using PAGE LOADABLE, size the NSE buffer cache (configuration section buffer_cache_cs, monitored in M_BUFFER_CACHE_STATISTICS). Check statement syntax against the SAP HANA SQL Reference for your SPS.

Get an NSE candidate list for your system

Send us your largest tables and access pattern and we will suggest which ones to page-enable and how to size the buffer cache.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Fill in the schema (often SAPHANADB or SAPABAP1) and table name, then run block 1 first: LOADED shows FULL, PARTIALLY or NO per partition, and the TABLES query shows the current unload priority and LOAD_UNIT (COLUMN or PAGE). Use block 2 to warm a table before a heavy job, block 3 to free memory now, block 4 to preload a critical table after restart, block 5 to make a table less or more likely to be displaced, and block 6 to move warm data to Native Storage Extension. Block 7 explains why a table was unloaded.

Unload is temporary: the next access loads the columns again.
Preload only tables that are needed immediately after a restart.
PAGE LOADABLE is a persistent change; plan it like a change request.

How HANA decides what stays in memory

Column tables are loaded column by column on first access, which is why LOADED is often PARTIALLY. When used memory approaches the allocation limit, HANA unloads columns, starting with tables with the highest unload priority and least recent use; M_CS_UNLOADS records these with reason LOW MEMORY, while your own UNLOAD statements show as EXPLICIT. Priority 0 protects a table entirely, which can push pressure onto everything else. PRELOAD marks columns to be loaded in the background after a restart. NSE changes the model: page loadable data is read in pages through a buffer cache, so only the hot pages occupy memory.

Worked example: CDPOS on S/4HANA

Block 1 shows CDPOS fully loaded at about 40 GB with priority 5, and nobody queries most of it. Unloading it (block 3) frees memory only until the next change-document read loads it again, so it is not a fix. Raising its unload priority to 7 lets HANA drop it before business-critical tables under pressure. The durable change is NSE: after confirming the buffer cache is sized, ALTER TABLE ... PAGE LOADABLE CASCADE keeps the table on disk with pages cached on demand, at the cost of slower full scans. Check block 1 again; LOAD_UNIT in TABLES should read PAGE.

Gotchas

Do not unload large tables during month-end close or a batch window: the reload costs I/O and CPU right when users need the data. A table set to PRELOAD ALL lengthens restart warm-up. Native Storage Extension needs HANA 2.0 SPS04 or later and a buffer cache sized for the warm working set; too small and queries slow down sharply. For SAP standard tables, prefer the candidates SAP documents for NSE and the NSE advisor recommendations. When only some partitions are page loadable, the table-level LOAD_UNIT does not tell the whole story, so check the partition and column level load units as well.

Frequently Asked Questions

How do I unload a table from memory in SAP HANA with SQL?

Run UNLOAD "SCHEMA"."TABLE"; in the tenant database. The table stays in the database and is loaded again on the next access, so it frees memory only temporarily. Check the effect with LOADED and MEMORY_SIZE_IN_TOTAL in M_CS_TABLES.

How do I load a table into HANA memory?

Run LOAD "SCHEMA"."TABLE" ALL; to load all columns, or LOAD with a column list to load only some. Loading in advance is useful before a large report or after a restart. To load automatically after every restart, set ALTER TABLE ... PRELOAD ALL.

How do I stop HANA from loading a table into memory?

You cannot keep an in-memory column table out of memory if it is queried. The options are a higher unload priority so it is displaced first, or Native Storage Extension with ALTER TABLE ... PAGE LOADABLE so only accessed pages are cached in the buffer cache. Archiving the data is the other route.

What does unload priority mean in SAP HANA?

It is a value from 0 to 9 that tells HANA which tables to unload first when memory runs short. 9 is unloaded earliest, 0 is never unloaded by memory pressure, and 5 is the usual default for column tables. You set it with ALTER TABLE ... UNLOAD PRIORITY n and see it in the TABLES view.

If you want to ask your SAP HANA system which tables are filling memory, in plain English and on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools