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

SAP HANA Memory Usage SQL Generator

This generator writes five SQL statements that show where SAP HANA memory goes: used memory against the allocation limit, resident memory on the host, heap allocators, the largest column tables and a daily history. It works for HANA 2.0 tenants under S/4HANA, Suite on HANA or BW.

Fill in

Top N rows

Service filter

History days

Schema for table list

Unit

Generated code

Download:

1. Used memory vs allocation limit per service (M_SERVICE_MEMORY)

SELECT M.HOST, M.PORT, M.SERVICE_NAME,
       ROUND(M.TOTAL_MEMORY_USED_SIZE / U.D, 2)     AS USED_GB,
       ROUND(M.PHYSICAL_MEMORY_SIZE / U.D, 2)       AS RESIDENT_GB,
       ROUND(M.HEAP_MEMORY_USED_SIZE / U.D, 2)      AS HEAP_USED_GB,
       ROUND(M.EFFECTIVE_ALLOCATION_LIMIT / U.D, 2) AS EFF_LIMIT_GB,
       ROUND(100 * M.TOTAL_MEMORY_USED_SIZE / NULLIF(M.EFFECTIVE_ALLOCATION_LIMIT, 0), 1) AS PCT_OF_LIMIT
  FROM M_SERVICE_MEMORY M,
       (SELECT CASE 'GB' WHEN 'GB' THEN 1073741824 ELSE 1048576 END AS D FROM DUMMY) U
 WHERE (M.SERVICE_NAME = 'indexserver' OR 'indexserver' = 'all')
 ORDER BY M.TOTAL_MEMORY_USED_SIZE DESC;

2. Host physical vs instance used memory (M_HOST_RESOURCE_UTILIZATION)

SELECT R.HOST,
       ROUND((R.USED_PHYSICAL_MEMORY + R.FREE_PHYSICAL_MEMORY) / U.D, 2) AS PHYSICAL_GB,
       ROUND(R.USED_PHYSICAL_MEMORY / U.D, 2)            AS OS_RESIDENT_GB,
       ROUND(R.FREE_PHYSICAL_MEMORY / U.D, 2)            AS FREE_GB,
       ROUND(R.ALLOCATION_LIMIT / U.D, 2)                AS ALLOC_LIMIT_GB,
       ROUND(R.INSTANCE_TOTAL_MEMORY_USED_SIZE / U.D, 2) AS INSTANCE_USED_GB
  FROM M_HOST_RESOURCE_UTILIZATION R,
       (SELECT CASE 'GB' WHEN 'GB' THEN 1073741824 ELSE 1048576 END AS D FROM DUMMY) U;

3. Top heap allocators (M_HEAP_MEMORY)

SELECT TOP 20 H.HOST, H.PORT, H.CATEGORY,
       ROUND(SUM(H.EXCLUSIVE_SIZE_IN_USE) / MAX(U.D), 2) AS IN_USE_GB
  FROM M_HEAP_MEMORY H,
       (SELECT CASE 'GB' WHEN 'GB' THEN 1073741824 ELSE 1048576 END AS D FROM DUMMY) U
 GROUP BY H.HOST, H.PORT, H.CATEGORY
 ORDER BY SUM(H.EXCLUSIVE_SIZE_IN_USE) DESC;

4. Largest column tables in memory (M_CS_TABLES)

SELECT TOP 20 T.SCHEMA_NAME, T.TABLE_NAME,
       COUNT(*)                                       AS PARTS,
       MAX(T.LOADED)                                  AS LOADED,
       SUM(T.RECORD_COUNT)                            AS RECORDS,
       ROUND(SUM(T.MEMORY_SIZE_IN_TOTAL) / MAX(U.D), 2) AS MEMORY_GB
  FROM M_CS_TABLES T,
       (SELECT CASE 'GB' WHEN 'GB' THEN 1073741824 ELSE 1048576 END AS D FROM DUMMY) U
 WHERE T.SCHEMA_NAME = 'SAPHANADB'
 GROUP BY T.SCHEMA_NAME, T.TABLE_NAME
 ORDER BY SUM(T.MEMORY_SIZE_IN_TOTAL) DESC;

5. Daily peak used memory for the last 7 days (_SYS_STATISTICS)

SELECT TO_DATE(S.SERVER_TIMESTAMP) AS DAY, S.HOST, S.PORT, S.SERVICE_NAME,
       ROUND(MAX(S.TOTAL_MEMORY_USED_SIZE) / MAX(U.D), 2) AS PEAK_USED_GB
  FROM _SYS_STATISTICS.HOST_SERVICE_MEMORY S,
       (SELECT CASE 'GB' WHEN 'GB' THEN 1073741824 ELSE 1048576 END AS D FROM DUMMY) U
 WHERE S.SERVER_TIMESTAMP >= ADD_DAYS(CURRENT_TIMESTAMP, -7)
   AND (S.SERVICE_NAME = 'indexserver' OR 'indexserver' = 'all')
 GROUP BY TO_DATE(S.SERVER_TIMESTAMP), S.HOST, S.PORT, S.SERVICE_NAME
 ORDER BY DAY, S.HOST, S.PORT;

Run in the tenant database (not SYSTEMDB) with a user that has CATALOG READ or the MONITORING role for the M_ views and SELECT on schema _SYS_STATISTICS for query 5. Check column names against the SAP HANA System Views Reference for your SPS. For deeper analysis use the HANA_Memory scripts from SAP Note 1969700 (SQL statement collection); SAP Note 1999997 is the HANA memory FAQ.

Get your memory results reviewed

Send us the output of the five queries and we will tell you whether you need archiving, NSE or more RAM.

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

Was this tool useful?

How to use it

Set the number of rows, the service filter, the schema of your SAP tables (SAPHANADB or SAPABAP1 are common), the history period and the unit. Paste the statements into the SQL console of HANA Cockpit, HANA Studio, DBA Cockpit (DBACOCKPIT) or hdbsql connected to the tenant. Start with query 1: if PCT_OF_LIMIT is close to 100 for the indexserver, HANA will unload tables and may raise out-of-memory dumps. Query 3 tells you whether the growth is in column store data (Pool/ColumnStore categories), statement memory or caches, and query 4 names the tables. Query 5 shows whether the peak is new or a trend.

Used memory vs resident memory

Used memory (TOTAL_MEMORY_USED_SIZE) is what HANA itself has in use: code, stack, heap and shared memory in use. It is the figure that counts against the allocation limit (global_allocation_limit) and that alerts and sizing use. Resident memory (PHYSICAL_MEMORY_SIZE per service, USED_PHYSICAL_MEMORY per host) is what the operating system reports as physically held by the process. Resident is normally higher than used because HANA keeps freed memory in its own pools for reuse instead of returning it to the OS at once. A high resident figure with comfortable used memory is not a problem by itself; a high used figure is.

Worked example: used memory is high

Query 1 shows the indexserver at 460 GB used against a 480 GB effective limit, 96 percent. Query 3 shows Pool/ColumnStore categories on top and a statement-related allocator second. Query 4 lists ACDOCA, CDPOS and BALDAT among the largest tables. The history in query 5 shows used memory climbing about 4 GB a day since month start. That points to data growth plus a heavy statement, not a leak. Actions: archive or move application logs and change documents, check the expensive statements trace, consider Native Storage Extension for warm data, and resize if the trend continues.

Gotchas

Run the queries in the tenant: in SYSTEMDB the M_ views show the system database, not your ERP data. M_HEAP_MEMORY categories are hierarchical, so always sum EXCLUSIVE_SIZE_IN_USE, never the inclusive columns, or you double count. M_CS_TABLES covers column tables only (unloaded ones show LOADED = NO and little memory); row store tables appear in M_RS_TABLES. The statistics history keeps a limited retention, so a long history period can return fewer days than asked. SERVER_TIMESTAMP is server local time. On multi-tenant systems a tenant indexserver can be capped by its own allocationlimit parameter (section memorymanager), so EFFECTIVE_ALLOCATION_LIMIT can sit well below the global limit.

Frequently Asked Questions

How do I check SAP HANA memory usage with SQL?

Query M_SERVICE_MEMORY for TOTAL_MEMORY_USED_SIZE and EFFECTIVE_ALLOCATION_LIMIT per service, and M_HOST_RESOURCE_UTILIZATION for host-level physical memory. For the breakdown, sum EXCLUSIVE_SIZE_IN_USE from M_HEAP_MEMORY by category and list MEMORY_SIZE_IN_TOTAL from M_CS_TABLES. Run them in the tenant database.

What is the difference between used and resident memory in SAP HANA?

Used memory is the memory HANA has in use and is measured against the allocation limit. Resident memory is the physical memory the operating system has assigned to the HANA processes, including memory HANA freed internally but has not returned to the OS. Resident is normally larger; monitor used memory for capacity.

How do I check HANA memory usage history?

The embedded statistics service stores snapshots in _SYS_STATISTICS. HOST_SERVICE_MEMORY has SERVER_TIMESTAMP and TOTAL_MEMORY_USED_SIZE per service, so a daily MAX over the last N days shows the trend. HANA Cockpit shows the same data graphically under memory usage.

What are the top memory consumers in SAP HANA?

Typically the column store tables themselves (largest S/4HANA tables are often ACDOCA, change documents, application logs and IDoc tables), followed by statement execution memory and caches. M_HEAP_MEMORY by category separates data from working memory, and M_CS_TABLES names the tables.

If you want your Basis team to ask HANA memory and table questions in plain English with the SQL shown, on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools