SAP BW/4HANA & Data Tiering with NSE - a Performance Comparison

Today we are talking about data management in your HANA system and what simple Data Tiering option you have available to save valuable memory that HANA works with. Not all objects need the power of limited RAM, learn in this blog post: 

• What NSE is and when to use it
• How easy it is to use
• How much performance loss you have (we measured)
• Tips and tricks in use.

Theory - What is Native Storage Extension (NSE)?

SAP NSE was first introduced with HANA 2.0 SPS04* and for the first time provides many customers with an easy way to move data from main memory to disk, usually without investing in new hardware. With just a few steps, this Data Tiering technology can save important main memory of the HANA database. NSE is a simpler and more up-to-date alternative to the previously known Extension Node.

Multi-Temperature Data Management

The following graphic classifies the technologies accordingly according to the concept of multi-temperature data management.

Multi-temperature data management in the context of volume, change frequency, performance and cost

• SAP HANA In-Memory represents the "hot" storage.
• SAP HANA Native Storage Extension or Extension Node represents the "warm" storage.
• SAP HANA Date Lake and SAP IQ act as the "cold" storage.

In this blog post, we look at the latest solution to implement a "warm" storage.

NSE Implementation

The implementation of NSE is very simple. Whereas previously a scale-out system had to be implemented with the Node extension, which meant increased maintenance effort, the Native Storage Extension is simply implemented as a scale-up system and scaled vertically. NSE is already available from HANA 2.0 SPS04 and can also be easily used in BW4/HANA modeling with SPS05, as shown in the following figure.

BW/4HANA: Data tiering options from SPS05

The use is possible out-of-the-box and very flexible. You can convert any data store such as aDSO to "warm" storage ("page loadable") at any time and just as easily make it available again as "hot" data ("column loadable") should the requirements for the availability of the data have changed. This is possible either on object level or on partition level.

Performance & the Buffer Cache

Query performance is slightly reduced for "warm" data compared to "hot" data. "Page loadable data is loaded into memory in granular units as required for query processing. The access to the data, e.g. for the query engine, is identical. To enable high performance, a so-called buffer cache is used, which allocates 10% of the total "hot" memory by default. Requested data from the "warm" memory is buffered accordingly and is thus available for reporting more quickly.

Here, some Dimensions must be taken into account in the implementation, since the volume of the "hot" memory should be a maximum of 4x and that of the "warm" memory a maximum of 8x as large as the volume of the buffer cache.

Example implementation of NSE on a HANA database with 2TB of memory

A comparison of SAP BW, HANA Native and SAP DW-Cloud - Download the Whitepaper here!

NSE Advisor - Identify suitable savings potentials

In order to find out which data stores offer a large savings potential in "hot" storage, the so-called NSE Advisor can be used. Suitable objects are to be identified. This means that the NSE Advisor is activated under a representative workload to check the performance of queries, memory utilization and the duration of processes. The goal of the heuristics is:

• Objects that are small and accessed frequently should be kept in memory to improve performance.
• Objects that are large and rarely accessed are better stored on disk to reduce memory consumption. These objects are fetched into memory only when needed and only for the pages that are accessed.

The result is a suggestion list with tables that offer savings potential and should be transferred to the "warm" memory. Experience has shown that a long runtime (e.g. one week) of the NSE Advisor leads to better results. For complex cases, however, a manual analysis is indispensable.

Test scenario - Are disadvantages due to NSE noticeable?

So much for the theory about NSE but how much memory is ultimately saved when NSE is introduced and the data temperature of the aDSO is maintained accordingly? Are performance losses noticeable in reporting?

We have gone to the bottom of these questions for you and have prepared two aDSO, which are identically constructed and are each available in "warm" memory and "hot" memory.

The two aDSO define themselves as follows:

• 1 key field of the data type CHAR
• 24 further fields of the data type CHAR
• 1 key figure of the data type DEC
• 5 million lines of test data in the active data table

The aDSO with the description Data-Temperature: hot is maintained with the data temperature "hot" and Data-Temperature: warm with the temperature "warm", as can be seen in the following screenshot. The size is almost identical, because there is no further compression of the data.

DTO cockpit: aDSO hot and aDSO warm in maintained state

In order to check the performance, a sufficiently complex query was created on each of the two aDSOs, which can then be checked for runtime. In SAP BW/4HANA, the evaluation is performed using the query CDS view Rv_C_OlapStatAQuery. This view contains the relevant fields with runtime information:

• TIMETOFRONTEND (runtime frontend)
• TIMEOLAP (runtime OLAP)
• TIMEDM (runtime data manager)
• TIMEPLAN (runtime planning)

Results

Full use of the buffer cache

The queries were started identically often via the query monitor. The average values determined for the run with full use of the buffer cache are as follows:

Results with full use of buffer cache *(rounded with Hot Query as base)

Without full use of the buffer cache

In the following test series, the buffer cache was artificially reduced to 5 megabytes in order to force access to the hard disk without a buffer cache. The average values determined are as follows:

Results without full use of buffer cache *(rounded with Hot Query as base)

Evaluation of the results

The results of the test show that performance suffers significantly when SAP NSE is used. Accessing the query via the buffer cache is associated with an increased runtime of 125% on average and even 138% without full use of the buffer cache.

Interesting facts

To check which objects are in the buffer cache or generally as "page loadable" there are some useful DDL statements that can be executed on the HANA database.

NSE - Our Conclusion

The Native Storage Extension is a simple way to save valuable HANA memory. This potential should be considered in any case, but especially on the lower layers of the LSA++.

Multi-Temperature Data Management in the LSA++ Context

Our recommendation for action clearly states an implementation on the staging layer or corporate memory layer. On layers with frequent access to data required for reporting, it is essential to consider the performance aspects from using SAP NSE, as these can vary depending on the scenario.

Implementing SAP NSE on a BW/4HANA system is easy to do and analyzing your own data stores is a worthwhile investment.

Do you have questions about HANA Native Storage Extension or about Multi-Temperature Data Management? Are you trying to build up the necessary know-how in your department or do you need support with a specific question? We are happy to help you with this. Request a non-binding consulting offer today.

*source: https://help.sap.com/docs/SAP_BW4HANA/