# SAP HANA Monitoring Views: Which M_ View Answers Which Question

> Source: https://netray.co/tools/sap-hana-monitoring-views-m-views-reference
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

One row per SAP HANA monitoring view with the key columns to select and the question it answers: memory, table size, disk, backups, locks, sessions and SQL.

## Category

sap

## Erp

SAP HANA

## Audience

- SAP HANA DBA
- SAP Basis administrator

## Tool

**Kind:** worksheet

**Mode:** reference

**Columns:**

#### View

**Key:** view

**Type:** text

#### What it shows

**Key:** shows

**Type:** text

#### Key columns

**Key:** columns

**Type:** text

#### Question it answers

**Key:** question

**Type:** text

#### Scope

**Key:** scope

**Type:** text

**Rows:**

#### Which HANA revision is this and when did it start?

**View:** M_DATABASE

**Shows:** Basic database information

**Columns:** SYSTEM_ID, DATABASE_NAME, VERSION, START_TIME

**Scope:** Tenant or SYSTEMDB

#### Are all tenants running?

**View:** M_DATABASES

**Shows:** All databases of the system with status

**Columns:** DATABASE_NAME, ACTIVE_STATUS

**Scope:** SYSTEMDB

#### Is the indexserver up, and which node is master?

**View:** M_SERVICES

**Shows:** Services per host

**Columns:** HOST, PORT, SERVICE_NAME, ACTIVE_STATUS, COORDINATOR_TYPE

**Scope:** Tenant or SYSTEMDB

#### How close is the indexserver to its allocation limit?

**View:** M_SERVICE_MEMORY

**Shows:** Memory per service

**Columns:** SERVICE_NAME, TOTAL_MEMORY_USED_SIZE, HEAP_MEMORY_USED_SIZE, SHARED_MEMORY_USED_SIZE, EFFECTIVE_ALLOCATION_LIMIT

**Scope:** Tenant or SYSTEMDB

#### How much physical memory is left on the host?

**View:** M_HOST_RESOURCE_UTILIZATION

**Shows:** Host memory and CPU

**Columns:** HOST, FREE_PHYSICAL_MEMORY, USED_PHYSICAL_MEMORY, ALLOCATION_LIMIT, INSTANCE_TOTAL_MEMORY_USED_SIZE

**Scope:** Tenant or SYSTEMDB

#### Which heap allocators are the top memory consumers?

**View:** M_HEAP_MEMORY

**Shows:** Heap allocators

**Columns:** HOST, PORT, CATEGORY, EXCLUSIVE_SIZE_IN_USE, EXCLUSIVE_ALLOCATED_SIZE

**Scope:** Tenant or SYSTEMDB

#### Which column tables use the most memory and are they fully loaded?

**View:** M_CS_TABLES

**Shows:** Column store tables and partitions in memory

**Columns:** 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

**Scope:** Tenant

#### How big is the row store and which tables drive it?

**View:** M_RS_TABLES

**Shows:** Row store tables

**Columns:** SCHEMA_NAME, TABLE_NAME, RECORD_COUNT, ALLOCATED_FIXED_PART_SIZE, ALLOCATED_VARIABLE_PART_SIZE

**Scope:** Tenant

#### Quick list of the largest tables in a schema

**View:** M_TABLES

**Shows:** All tables with size and type

**Columns:** SCHEMA_NAME, TABLE_NAME, RECORD_COUNT, TABLE_SIZE, IS_COLUMN_TABLE

**Scope:** Tenant

#### How much disk does each table occupy (not memory)?

**View:** M_TABLE_PERSISTENCE_STATISTICS

**Shows:** Disk size of tables

**Columns:** SCHEMA_NAME, TABLE_NAME, DISK_SIZE

**Scope:** Tenant

#### Were tables unloaded because of memory pressure (REASON LOW MEMORY)?

**View:** M_CS_UNLOADS

**Shows:** History of column table unloads

**Columns:** UNLOAD_TIME, SCHEMA_NAME, TABLE_NAME, REASON

**Scope:** Tenant

#### Are merges failing or taking too long on a table?

**View:** M_DELTA_MERGE_STATISTICS

**Shows:** Delta merge history

**Columns:** START_TIME, TABLE_NAME, TYPE, MOTIVATION, EXECUTION_TIME, SUCCESS

**Scope:** Tenant

#### Are long savepoint critical phases causing short freezes?

**View:** M_SAVEPOINTS

**Shows:** Savepoint history

**Columns:** START_TIME, DURATION, CRITICAL_PHASE_DURATION, TOTAL_SIZE

**Scope:** Tenant or SYSTEMDB

#### Is the log volume filling because segments are not freed (backup or replication)?

**View:** M_LOG_SEGMENTS

**Shows:** Redo log segments

**Columns:** HOST, PORT, STATE, TOTAL_SIZE, USED_SIZE

**Scope:** Tenant or SYSTEMDB

#### How large are data volumes and how much is used?

**View:** M_VOLUME_FILES

**Shows:** Data and log volume files

**Columns:** HOST, PORT, FILE_TYPE, FILE_NAME, USED_SIZE, TOTAL_SIZE

**Scope:** Tenant or SYSTEMDB

#### How full are the data, log and trace file systems?

**View:** M_DISKS

**Shows:** Disks used by the database

**Columns:** HOST, PATH, USAGE_TYPE, TOTAL_SIZE, USED_SIZE

**Scope:** Tenant or SYSTEMDB

#### How much disk goes to data, log, trace and backup?

**View:** M_DISK_USAGE

**Shows:** Disk usage by usage type

**Columns:** HOST, USAGE_TYPE, USED_SIZE

**Scope:** Tenant or SYSTEMDB

#### When was the last successful complete data backup?

**View:** M_BACKUP_CATALOG

**Shows:** Backup catalog entries

**Columns:** BACKUP_ID, ENTRY_TYPE_NAME, SYS_START_TIME, SYS_END_TIME, STATE_NAME

**Scope:** Tenant or SYSTEMDB

#### How big was the backup and where was it written?

**View:** M_BACKUP_CATALOG_FILES

**Shows:** Files of each backup

**Columns:** BACKUP_ID, SOURCE_TYPE_NAME, BACKUP_SIZE, DESTINATION_PATH

**Scope:** Tenant or SYSTEMDB

#### Who is connected and from where?

**View:** M_CONNECTIONS

**Shows:** Client connections

**Columns:** CONNECTION_ID, USER_NAME, CLIENT_HOST, CONNECTION_STATUS, START_TIME

**Scope:** Tenant

#### What is running right now and how much memory does it hold?

**View:** M_ACTIVE_STATEMENTS

**Shows:** Statements currently executing

**Columns:** CONNECTION_ID, STATEMENT_STRING, STATEMENT_STATUS, ALLOCATED_MEMORY_SIZE

**Scope:** Tenant

#### Which statements were slow or memory-heavy? (trace must be enabled)

**View:** M_EXPENSIVE_STATEMENTS

**Shows:** Statements over the expensive statements trace threshold

**Columns:** START_TIME, DURATION_MICROSEC, STATEMENT_STRING, MEMORY_SIZE, DB_USER

**Scope:** Tenant

#### Which statements consume the most total time?

**View:** M_SQL_PLAN_CACHE

**Shows:** Cached statements with execution statistics

**Columns:** STATEMENT_HASH, STATEMENT_STRING, EXECUTION_COUNT, TOTAL_EXECUTION_TIME, AVG_EXECUTION_TIME

**Scope:** Tenant

#### Who blocks whom right now?

**View:** M_BLOCKED_TRANSACTIONS

**Shows:** Transactions waiting for locks

**Columns:** BLOCKED_TRANSACTION_ID, LOCK_OWNER_TRANSACTION_ID, BLOCKED_TIME, WAITING_TABLE_NAME, LOCK_TYPE

**Scope:** Tenant

#### Are there long-open transactions holding MVCC versions?

**View:** M_TRANSACTIONS

**Shows:** Open transactions

**Columns:** TRANSACTION_ID, CONNECTION_ID, TRANSACTION_STATUS, START_TIME

**Scope:** Tenant

#### Is system replication active and in sync?

**View:** M_SERVICE_REPLICATION

**Shows:** System replication status per service

**Columns:** HOST, PORT, SECONDARY_HOST, REPLICATION_MODE, REPLICATION_STATUS

**Scope:** Tenant or SYSTEMDB

#### Is the license valid and how close is usage to the limit?

**View:** M_LICENSE

**Shows:** Installed license

**Columns:** HARDWARE_KEY, EXPIRATION_DATE, PERMANENT, VALID, PRODUCT_LIMIT, PRODUCT_USAGE

**Scope:** SYSTEMDB

#### What is global_allocation_limit or statement_memory_limit set to, and at which layer?

**View:** M_INIFILE_CONTENTS

**Shows:** Configuration parameters by layer

**Columns:** FILE_NAME, LAYER_NAME, SECTION, KEY, VALUE

**Scope:** Tenant or SYSTEMDB

#### How did CPU and memory develop over the last hours?

**View:** M_LOAD_HISTORY_SERVICE

**Shows:** Load history per service (the HANA cockpit load graph)

**Columns:** TIME, HOST, PORT, CPU, MEMORY_USED, MEMORY_ALLOCATION_LIMIT

**Scope:** Tenant or SYSTEMDB

#### On which node does a table live?

**View:** M_TABLE_LOCATIONS

**Shows:** Where tables are located in scale-out

**Columns:** SCHEMA_NAME, TABLE_NAME, HOST, PORT, LOCATION

**Scope:** Tenant

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to check HANA database size and memory

**Body:** 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

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### How do I check SAP HANA memory usage?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

sap hana memory monitoring, how to check hana db size, sap hana table memory usage, sap hana top memory consumers, sap hana troubleshooting and analysis guide, m_service_memory, m_cs_tables memory size, hana monitoring views

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json