Skip to Content
Technical Articles
Author's profile photo Yogananda Muthaiah

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.

Assigned tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Vishwanath Shastri
      Vishwanath Shastri

      Good one,

      Short and Sweet

      Author's profile photo Vijaya Rayapudi
      Vijaya Rayapudi

      Useful content. Thank you for sharing. 🙂

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Thanks Vijaya Rayapudi