There are several activities that have to be performed to keep your SAP HANA database running without problems and as efficiently as possible. Keeping it updated, checking that its configured parameters align to SAP’s recommendations, and monitoring memory and CPU usage are a few of them. Just as important is periodically checking the need to partition the system’s largest tables.
As with any database, SAP HANA has some limits on the number of tables it can contain, the maximum number of columns a table can have, and the maximum number of records a single table can reach. This last limit is usually the easiest to reach: 2,147,483,648 rows:
Max. number of columns in row store table | 1000 |
Max. number of columns in column store table | 64000 |
Max. number of row store tables | Limited to a total of 1,945 GB (sum of all row store tables) |
Max. number of column store tables | Unlimited but depends on physical memory |
Max. number of records per table in row store | Unlimited |
Max. number of records per table in column store (per table or partition) | 2,147,483,648 records (2,100,000,000 as of SAP HANA 2.0 SPS07) |
This might seem like a large number, but it’s not hard to reach on a SAP ERP or S/4HANA system, so it’s very important to monitor the database for this indicator. When a table is approaching this limit, a message will be added to the database alerts, indicating the table’s name and the current number of records in it.
To avoid a problem when reaching this limit, SAP HANA provides the option to partition a table. This means that the table is split into smaller sub-tables or partitions, divided by some key field(s). If partitioning by HASH, each row will be assigned to a partition based on a calculation of a hash on its partitioning column. SAP HANA also supports partitioning by RANGE, so a table can be partitioned by, for example, the fiscal year, a quarter, or a set of customers. This way, the database can avoid loading into memory the partitions that are not used as frequently, like the ones for previous years, improving memory usage and general system performance.
SAP provides some recommendations for partitioning SAP ERP and S/4HANA tables in SAP Note "2044468 - FAQ: SAP HANA Partitioning". For example, table BSEG should be partitioned by HASH on the BELNR column. If you have a customer table that has a date field, and older records are not frequently read, it should be worthwhile partitioning it by RANGE on this column, so that you can force older partitions to stay on disk using SAP HANA's NSE feature. SAP also recommends keeping the number of partitions low on each table, as there is some memory overhead when running queries over partitioned tables.
It could also be useful to partition tables that don't have that many records but that still use a significant amount of memory. This way, you can keep memory requirements low during the Delta Merge process that HANA executes for column store tables. During this process, a table doubles its memory usage because it exists twice. Partitioning can be especially helpful on systems with lower amounts of memory: we have seen cases where partitioning a 40GB table on a system with 160GB of RAM has helped avoid out-of-memory situations, as this 40GB table can use more than 80GB during its Delta Merge. If this happens during high system load, the database can run out of memory.
Partitioning tables is a necessary housekeeping activity on SAP HANA after your database reaches a certain size. If you need any help in analyzing your system and implementing this activity, don't hesitate to contact us.