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

SAP HANA Monitoring Views: Which M_ View Answers Which Question

This reference lists the SAP HANA 2.0 monitoring views (the M_ system views in schema SYS) that DBAs and Basis admins use most, with the key columns to select and the question each one answers. Use it to write quick SQL in HANA Studio, the SQL console of HANA cockpit or hdbsql.

Download:

View	What it shows	Key columns	Question it answers	Scope
M_DATABASE	Basic database information	SYSTEM_ID, DATABASE_NAME, VERSION, START_TIME	Which HANA revision is this and when did it start?	Tenant or SYSTEMDB
M_DATABASES	All databases of the system with status	DATABASE_NAME, ACTIVE_STATUS	Are all tenants running?	SYSTEMDB
M_SERVICES	Services per host	HOST, PORT, SERVICE_NAME, ACTIVE_STATUS, COORDINATOR_TYPE	Is the indexserver up, and which node is master?	Tenant or SYSTEMDB
M_SERVICE_MEMORY	Memory per service	SERVICE_NAME, TOTAL_MEMORY_USED_SIZE, HEAP_MEMORY_USED_SIZE, SHARED_MEMORY_USED_SIZE, EFFECTIVE_ALLOCATION_LIMIT	How close is the indexserver to its allocation limit?	Tenant or SYSTEMDB
M_HOST_RESOURCE_UTILIZATION	Host memory and CPU	HOST, FREE_PHYSICAL_MEMORY, USED_PHYSICAL_MEMORY, ALLOCATION_LIMIT, INSTANCE_TOTAL_MEMORY_USED_SIZE	How much physical memory is left on the host?	Tenant or SYSTEMDB
M_HEAP_MEMORY	Heap allocators	HOST, PORT, CATEGORY, EXCLUSIVE_SIZE_IN_USE, EXCLUSIVE_ALLOCATED_SIZE	Which heap allocators are the top memory consumers?	Tenant or SYSTEMDB
M_CS_TABLES	Column store tables and partitions in memory	SCHEMA_NAME, TABLE_NAME, PART_ID, MEMORY_SIZE_IN_TOTAL, MEMORY_SIZE_IN_MAIN, MEMORY_SIZE_IN_DELTA, RECORD_COUNT, LOADED, ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL	Which column tables use the most memory and are they fully loaded?	Tenant
M_RS_TABLES	Row store tables	SCHEMA_NAME, TABLE_NAME, RECORD_COUNT, ALLOCATED_FIXED_PART_SIZE, ALLOCATED_VARIABLE_PART_SIZE	How big is the row store and which tables drive it?	Tenant
M_TABLES	All tables with size and type	SCHEMA_NAME, TABLE_NAME, RECORD_COUNT, TABLE_SIZE, IS_COLUMN_TABLE	Quick list of the largest tables in a schema	Tenant
M_TABLE_PERSISTENCE_STATISTICS	Disk size of tables	SCHEMA_NAME, TABLE_NAME, DISK_SIZE	How much disk does each table occupy (not memory)?	Tenant
M_CS_UNLOADS	History of column table unloads	UNLOAD_TIME, SCHEMA_NAME, TABLE_NAME, REASON	Were tables unloaded because of memory pressure (REASON LOW MEMORY)?	Tenant
M_DELTA_MERGE_STATISTICS	Delta merge history	START_TIME, TABLE_NAME, TYPE, MOTIVATION, EXECUTION_TIME, SUCCESS	Are merges failing or taking too long on a table?	Tenant
M_SAVEPOINTS	Savepoint history	START_TIME, DURATION, CRITICAL_PHASE_DURATION, TOTAL_SIZE	Are long savepoint critical phases causing short freezes?	Tenant or SYSTEMDB
M_LOG_SEGMENTS	Redo log segments	HOST, PORT, STATE, TOTAL_SIZE, USED_SIZE	Is the log volume filling because segments are not freed (backup or replication)?	Tenant or SYSTEMDB
M_VOLUME_FILES	Data and log volume files	HOST, PORT, FILE_TYPE, FILE_NAME, USED_SIZE, TOTAL_SIZE	How large are data volumes and how much is used?	Tenant or SYSTEMDB
M_DISKS	Disks used by the database	HOST, PATH, USAGE_TYPE, TOTAL_SIZE, USED_SIZE	How full are the data, log and trace file systems?	Tenant or SYSTEMDB
M_DISK_USAGE	Disk usage by usage type	HOST, USAGE_TYPE, USED_SIZE	How much disk goes to data, log, trace and backup?	Tenant or SYSTEMDB
M_BACKUP_CATALOG	Backup catalog entries	BACKUP_ID, ENTRY_TYPE_NAME, SYS_START_TIME, SYS_END_TIME, STATE_NAME	When was the last successful complete data backup?	Tenant or SYSTEMDB
M_BACKUP_CATALOG_FILES	Files of each backup	BACKUP_ID, SOURCE_TYPE_NAME, BACKUP_SIZE, DESTINATION_PATH	How big was the backup and where was it written?	Tenant or SYSTEMDB
M_CONNECTIONS	Client connections	CONNECTION_ID, USER_NAME, CLIENT_HOST, CONNECTION_STATUS, START_TIME	Who is connected and from where?	Tenant
M_ACTIVE_STATEMENTS	Statements currently executing	CONNECTION_ID, STATEMENT_STRING, STATEMENT_STATUS, ALLOCATED_MEMORY_SIZE	What is running right now and how much memory does it hold?	Tenant
M_EXPENSIVE_STATEMENTS	Statements over the expensive statements trace threshold	START_TIME, DURATION_MICROSEC, STATEMENT_STRING, MEMORY_SIZE, DB_USER	Which statements were slow or memory-heavy? (trace must be enabled)	Tenant
M_SQL_PLAN_CACHE	Cached statements with execution statistics	STATEMENT_HASH, STATEMENT_STRING, EXECUTION_COUNT, TOTAL_EXECUTION_TIME, AVG_EXECUTION_TIME	Which statements consume the most total time?	Tenant
M_BLOCKED_TRANSACTIONS	Transactions waiting for locks	BLOCKED_TRANSACTION_ID, LOCK_OWNER_TRANSACTION_ID, BLOCKED_TIME, WAITING_TABLE_NAME, LOCK_TYPE	Who blocks whom right now?	Tenant
M_TRANSACTIONS	Open transactions	TRANSACTION_ID, CONNECTION_ID, TRANSACTION_STATUS, START_TIME	Are there long-open transactions holding MVCC versions?	Tenant
M_SERVICE_REPLICATION	System replication status per service	HOST, PORT, SECONDARY_HOST, REPLICATION_MODE, REPLICATION_STATUS	Is system replication active and in sync?	Tenant or SYSTEMDB
M_LICENSE	Installed license	HARDWARE_KEY, EXPIRATION_DATE, PERMANENT, VALID, PRODUCT_LIMIT, PRODUCT_USAGE	Is the license valid and how close is usage to the limit?	SYSTEMDB
M_INIFILE_CONTENTS	Configuration parameters by layer	FILE_NAME, LAYER_NAME, SECTION, KEY, VALUE	What is global_allocation_limit or statement_memory_limit set to, and at which layer?	Tenant or SYSTEMDB
M_LOAD_HISTORY_SERVICE	Load history per service (the HANA cockpit load graph)	TIME, HOST, PORT, CPU, MEMORY_USED, MEMORY_ALLOCATION_LIMIT	How did CPU and memory develop over the last hours?	Tenant or SYSTEMDB
M_TABLE_LOCATIONS	Where tables are located in scale-out	SCHEMA_NAME, TABLE_NAME, HOST, PORT, LOCATION	On which node does a table live?	Tenant

M_ views show current state since the last restart. For history, use the statistics server tables and views in schema _SYS_STATISTICS (for example HOST_SERVICE_MEMORY, HOST_HEAP_ALLOCATORS). From SYSTEMDB, schema SYS_DATABASES offers many M_ views across all tenants with a DATABASE_NAME column. Sizes are in bytes. Column lists vary slightly by SPS: confirm in the SAP HANA SQL and System Views Reference for your revision.

HANA close to its allocation limit?

Send us the output of the memory views and we will return a reviewed list of the top consumers and what to do about each.

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

Was this tool useful?

How to check HANA database size and memory

There are three different sizes and people mix them up. Memory used by the database is in M_SERVICE_MEMORY (TOTAL_MEMORY_USED_SIZE per service, against EFFECTIVE_ALLOCATION_LIMIT) and at host level in M_HOST_RESOURCE_UTILIZATION. Table data in memory is in M_CS_TABLES (MEMORY_SIZE_IN_TOTAL) and M_RS_TABLES. Data on disk is in M_TABLE_PERSISTENCE_STATISTICS (DISK_SIZE) and M_VOLUME_FILES for the volumes. A licence or sizing question usually needs memory; a storage question needs disk. Sum column store memory with SELECT SCHEMA_NAME, ROUND(SUM(MEMORY_SIZE_IN_TOTAL)/1024/1024/1024, 2) AS GB FROM M_CS_TABLES GROUP BY SCHEMA_NAME ORDER BY GB DESC.

Worked example: who are the top memory consumers

The indexserver is at 92 percent of its allocation limit. Step 1: SELECT SERVICE_NAME, TOTAL_MEMORY_USED_SIZE, EFFECTIVE_ALLOCATION_LIMIT FROM M_SERVICE_MEMORY confirms the indexserver. Step 2: M_HEAP_MEMORY ordered by EXCLUSIVE_SIZE_IN_USE descending shows whether column store tables (allocator Pool/ColumnStore) or something else, such as SQL execution or caches, dominate. Step 3: if it is tables, M_CS_TABLES ordered by MEMORY_SIZE_IN_TOTAL shows the top tables and MEMORY_SIZE_IN_DELTA reveals delta that needs a merge. Step 4: if it is execution memory, M_ACTIVE_STATEMENTS and M_EXPENSIVE_STATEMENTS show the statements; statement_memory_limit in M_INIFILE_CONTENTS shows whether a cap is set.

Large MEMORY_SIZE_IN_DELTA: check M_DELTA_MERGE_STATISTICS
LOADED = PARTIALLY or NO: tables unloaded, check M_CS_UNLOADS
Repeated LOW MEMORY unloads: sizing or a runaway statement

Tenant versus SYSTEMDB

In a multitenant system each tenant's SYS schema shows only that tenant. Connect to SYSTEMDB for M_DATABASES and for licence information, and use the SYS_DATABASES schema there to query many M_ views across tenants at once with a DATABASE_NAME column. Host-level views such as M_HOST_RESOURCE_UTILIZATION show the whole host, which is shared by all tenants, so do not read host free memory as tenant free memory.

Gotchas

M_ views reset on restart; for trends use _SYS_STATISTICS. M_EXPENSIVE_STATEMENTS is empty unless the expensive statements trace is enabled with a threshold. M_SQL_PLAN_CACHE is evicted under pressure, so a statement can disappear. Sizes are bytes. Querying M_CS_TABLES on a very large system can itself take time; filter by schema. Finally, SAP provides a curated SQL statement collection (SAP Note 1969700) that builds on these views and is worth using before writing your own.

Frequently Asked Questions

How do I check SAP HANA memory usage?

Query M_SERVICE_MEMORY for TOTAL_MEMORY_USED_SIZE and EFFECTIVE_ALLOCATION_LIMIT per service, and M_HOST_RESOURCE_UTILIZATION for host-level used and free memory. M_HEAP_MEMORY breaks usage down by allocator to find the top consumers.

How do I check the HANA DB size?

For memory size of table data, sum MEMORY_SIZE_IN_TOTAL from M_CS_TABLES plus row store sizes from M_RS_TABLES. For disk size, use M_TABLE_PERSISTENCE_STATISTICS (DISK_SIZE) per table or M_VOLUME_FILES for the data volumes. The last complete backup size in M_BACKUP_CATALOG_FILES is another practical measure.

How do I see table memory usage in SAP HANA?

Select SCHEMA_NAME, TABLE_NAME, MEMORY_SIZE_IN_TOTAL, MEMORY_SIZE_IN_DELTA, RECORD_COUNT and LOADED from M_CS_TABLES, ordered by MEMORY_SIZE_IN_TOTAL descending. Partitioned tables return one row per partition, so group by table to get totals.

Where is historical HANA monitoring data?

The embedded statistics service writes history into schema _SYS_STATISTICS, for example HOST_SERVICE_MEMORY for service memory over time. M_LOAD_HISTORY_SERVICE also holds recent load history used by HANA cockpit. M_ views themselves only show current state since restart.

If you want your DBAs to ask HANA what is using memory in plain English and get the SQL behind the answer, on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools