SAP Commissions: How to know your HANA Database System Limitation

Dear All ,

You can use below HANA SQL System view to get the list of system limitation of your SAP HANA database tenant.

This system limitation values is important and need to take into consideration for HANA database administration. Please be noted that the values might differ depending on the hardware and software configuration your system uses.

M_SYSTEM_LIMITS

It will provide you details about

Database Limitation
Schema Limitations
Tables and View Limitation
Indexes and Constraints
SQL
SQL Script

Reference output from the executed command.

SELECT * FROM M_SYSTEM_LIMITS;

Limitation Area	Limit	M_SYSTEM_LIMITS view name for the limitation
Database size limit	Row Store: 1,945 GB Column Store: Dependent on size of physical memory	MAXIMUM_SIZE_OF_ROW_STORE
Number of locks	Unlimited for record locks, 16,383 for table locks	MAXIMUM_NUMBER_OF_TABLE_LOCKS
Number of sessions	65,536	MAXIMUM_NUMBER_OF_SESSIONS

Schema Limitations
Number of schemas per SAP HANA instance	Maximum value of BIGINT data type
Identifier length	127 characters	MAXIMUM_LENGTH_OF_IDENTIFIER
Length of an alias name	128 characters	MAXIMUM_LENGTH_OF_ALIAS_NAME
Table name length	Same as Identifier length	MAXIMUM_LENGTH_OF_IDENTIFIER
Column name length	Same as Identifier length	MAXIMUM_LENGTH_OF_IDENTIFIER
Length of a string literal	8 MB	MAXIMUM_LENGTH_OF_STRING_LITERAL
Number of hex characters in a binary literal	8,192 Bytes	MAXIMUM_LENGTH_OF_BINARY_LITERAL

Tables and View Limitations
Number of columns in a table	64,000 This limit can vary based on context, for example, in the context of virtual tables, SAP HANA may be limited by the capabilities of the remote system and the limit of the other DBMS may apply instead. In cases such as this, the limit that is met first becomes the actual limit.	MAXIMUM_NUMBER_OF_COLUMNS_IN_TABLE
Number of columns in a row table	1,000	MAXIMUM_NUMBER_OF_COLUMNS_IN_ROW_TABLE
Number of columns in a view	64,000	MAXIMUM_NUMBER_OF_COLUMNS_IN_VIEW
Number of rows in each table	Limited by storage size RS: 1,945 GB/sizeof(row), CS: 2^31 * number of partitions
Length of a row	Limited by RS storage size (1,945 GB per index server)
Size of a non-partitioned table	Limited by RS storage size (1,945 GB per index server)
Number of partitions in a CS table	16,000	MAXIMUM_NUMBER_OF_PARTITIONS_IN_CSTABLE
Number of triggers per table per DML statement	1,024	MAXIMUM_NUMBER_OF_TRIGGERS_PER_TABLE_PER_DML
Number of records per (non-partitioned) table	2^31 (i.e. 2,147,384,648, or ~2 billion)

Indexes and Constraints
Number of indexes for a table	1,023	MAXIMUM_NUMBER_OF_INDEXES_IN_TABLE
Number of primary key columns in each table	16	MAXIMUM_NUMBER_OF_COLUMNS_IN_PRIMARY_KEY
Number of primary key columns in each column store table	1,000	MAXIMUM_NUMBER_OF_COLUMNS_IN_PRIMARY_KEY_IN_COLUMN_TABLE
Number of columns in an index	16	MAXIMUM_NUMBER_OF_COLUMNS_IN_INDEX
Number of columns in a UNIQUE constraint	16	MAXIMUM_NUMBER_OF_COLUMNS_IN_UNIQUE_CONSTRAINT
Size of sum of primary key, index, UNIQUE constraint	16,384 Bytes	MAXIMUM_SIZE_OF_KEY_IN_INDEX
Number of indexes in row store	256,000

SQL
Length of an SQL statement	2,147,483,648 Bytes	MAXIMUM_LENGTH_OF_SQL_STATEMENT
Depth of SQL view nesting	128	MAXIMUM_DEPTH_OF_SQL_VIEW_NESTING
Maximum depth of SQL parse tree This limitation does not show in M_SYSTEM_LIMITS unless a limit is configured to something other than 0 (no limit) using the max_parse_tree_depth parameter in indexerver.ini.	0 0 (unlimited)	MAXIMUM_DEPTH_OF_SQL_PARSE_TREE
Maximum depth of joins in a statement. This limitation does not show in M_SYSTEM_LIMITS unless a limit is configured to something other than 0 (no limit) using the max_join_depth parameter in indexerver.ini.	0	MAXIMUM_DEPTH_OF_JOINS
Number of columns in an ORDER BY	65,535	MAXIMUM_NUMBER_OF_COLUMNS_IN_ORDER_BY
Number of columns in a GROUP BY	65,535	MAXIMUM_NUMBER_OF_COLUMNS_IN_GROUP_BY
Number of elements in IN predicates	65,535	MAXIMUM_NUMBER_OF_COLUMNS_IN_IN_PREDICATE
Number of elements in SELECT clause	65,535	MAXIMUM_NUMBER_OF_OUTPUT_COLUMNS_IN_STATEMENT
Number of tables in a statement. This limitation does not show in M_SYSTEM_LIMITS unless a limit is configured to something other than 0 (no limit) using the max_table_count_in_statement parameter in indexerver.ini.	0	MAXIMUM_NUMBER_OF_TABLES_IN_STATEMENT

LOB Limitations
Maximum size of an in-memory LOB for a column store table	1,073,741,821 bytes	MAXIMUM_SIZE_OF_MEMORY_LOB_IN_COLUMN_STORE
Maximum size of an in-memory LOB for a row store table	2,147,463,647 bytes	MAXIMUM_SIZE_OF_MEMORY_LOB_IN_ROW_STORE
Maximum size of a packed LOB	1,013,760 bytes	MAXIMUM_SIZE_OF_PACKED_LOB
Maximum size of a LOB on disk	4,294,967,295 bytes	MAXIMUM_SIZE_OF_DISK_LOB

Procedures
Size of all stored procedures	1,945 GB	MAXIMUM_SIZE_OF_ALL_STORED_PROCEDURES
Size of a procedure definition	2 GB	MAXIMUM_SIZE_OF_PROCEDURE_DEFINITION

Thank you for reading!. I feel much better. It’s good to be liked. (Hit Like Button).

Kindly leave your comments for more clarification

you can also share this blog through LinkedIn or twitter from clicking below icons.

3 Comments

You must be Logged on to comment or reply to a post.

Vishwanath Shastri

September 1, 2020 at 5:40 pm

Good one,

Short and Sweet

Vijaya Rayapudi

November 28, 2020 at 5:08 am

Useful content. Thank you for sharing. 🙂

Yogananda Muthaiah

Blog Post Author

November 29, 2020 at 10:59 am

Thanks Vijaya Rayapudi

Technical Articles

SAP Commissions: How to know your HANA Database System Limitation

Assigned Tags