If you're an SAP professional or an enterprise leveraging SAP HANA, you might have come across the term 'NSE advisor' and wondered what it is, how it works, and how it can benefit your organization. In this blog post, we aim to answer some frequently asked questions that will help you make the most of this tool and gradually transfer your tables into NSE (Native Storage Extension) with the help of the NSE advisor. For more information, you can also refer to the the official documentation and the whitepaper.
Q1: What is the NSE advisor?
The NSE advisor is a tool that provides recommendations on load units for tables, partitions, or columns, based on their access frequency during a given workload. It helps you determine which data should be loaded into the buffer cache (NSE) for quick access (page-loadable) and which data should be kept in memory using columnar storage to enhance performance (column-loadable).
The NSE advisor assesses the temperature of the data based on its access frequency and uses rule-based heuristics to identify hot and warm objects as candidates for these optimizations.
Q2: How to configure the NSE advisor for the first use?
There are several ways to run the NSE advisor. In this blogpost, we refer to using the built-in procedure. For more information, see Using the NSE Advisor with SQL Commands (Built-in Procedure) for Warm Data Recommendations.
To make the most of the NSE advisor you need to be familiar with your data-access patterns and understand your workload to find out when it is the most beneficial time to run it. However, you might not have this knowledge yet, so you should begin with somehow conservative settings. For example, set the HOT_OBJECT_THRESHOLD to 15%, the COLD_OBJECT_THRESHOLD to 15%, and set the DURATION value in seconds.
The DURATION is normally defined according to your workload; customers’ workloads normally have periodicity, so the DURATION should cover one cycle of your workload. To decide the value for DURATION, you should consider your primary business usage time. If you are not sure of your primary business usage time, you can analyze it with M_LOAD_HISTORY_HOST, M_LOAD_HISTORY_INFO, or M_LOAD_HISTORY_SERVICE.
In this blogpost we’ll assume your business usage time is 1 day (86400 seconds). So you can run the following statement: CALL SYS.CS_NSE_ADVISOR(ACTION=>'ENABLE', HOT_OBJECT_THRESHOLD=>'15', COLD_OBJECT_THRESHOLD=>'15', MIN_OBJECT_SIZE=>'1048576', DURATION=>'86400', REQUEST_ID=>?)
Q3: How to apply recommendations?
By now, you should have your first set of recommendations. You can retrieve the recommendations from the M_CS_NSE_ADVISOR view. Pay attention to the "confidence" value, which helps you to prioritize what recommendations to apply first. Recommendations should not be implemented all at once, rather they should be applied step by step, starting with those with the highest confidence value.
To diminish disruption, implement recommendations during your system's downtime or when the system isn't heavily in use—for example, early morning or late night, depending on your business hours. After applying the recommendations, you can monitor the performance of your SAP HANA servers by one or two cycles of your workload, for example, 1 or 2 days. See Q4 of this blog post.
After applying each recommendation you should verify and adjust the maximum NSE buffer cache size, as well as the unload_threshold parameter value (if not set to be automatically calculated).
If you encounter performance issues on a table (maybe because it changed to PAGE LOADABLE and has a low performance), you can revert its load unit and apply a recommendation rule on it. This way, the NSE advisor will not suggest recommendations for that table anymore. See Q6 from this blog post.
Q4: How can I determine if my configuration settings are fine-tuned to suit my workload?
A fine-tuned configuration balances performance and memory usage efficiently. Here are some indicators:
- Query Performance: You may notice slightly reduced performance on warm data (PAGE LOADABLE) compared to hot data (COLUMN LOADABLE), but this is expected as it consumes less memory.
- Functionality: All tables can theoretically be put into Native Store Extension (NSE) without functional errors. However, your applications should still perform adequately.
- Resource Management: A well-configured system allows you to place a sufficient number of tables into NSE without encountering performance degradation or out-of-memory issues.
If you struggle to achieve a satisfactory cost/performance balance using NSE features for your workload, it might be beneficial to consider upgrading your SAP HANA system with additional CPU or memory resources.
Q5: How I can I fine-tune configuration settings to fit my workload?
To get a better feel of how the advisor works, enable it for a period, apply the recommendations according to your needs, and monitor the impact they have on the performance of your instances. With this information in mind, you can gradually increase the value of the HOT_OBJECT_THRESHOLD or COLD_OBJECT_THRESHOLD. For example, you can set the starting value to 15%, in the second round you can increase it to 20%, and in the third round to 25%. Modify these values and apply recommendations until you achieve a balance of cost/performance in your system.
Q6: Can the advisor provide unexpected recommendations?
Yes, it is possible to receive unusual recommendations from the advisor under certain circumstances. The Native Store Extension (NSE) advisor uses access counters and size information to analyse access patterns in your workload. It relies on two thresholds to distinguish between hot and cold areas of your tables. Here are a couple of situations where recommendations might fluctuate:
- Borderline Cases: Some tables may lie near the boundaries of these hot and cold areas. As a result, the advisor's recommendations for these objects might change frequently.
- Dynamic Workloads: If your workload patterns change frequently or cyclically, the advisor's recommendations may also vary to reflect these shifts.
Therefore, it is essential to monitor and evaluate the advisor's recommendations in the context of your specific workload and installed configuration.
If you want to exclude certain tables, partitions, or columns from getting recommendations, you can use recommendation rules. For more information, see Recommendation Rules.
We hope these answers help you get started with the NSE advisor. Do you have more questions? Feel free to leave them in the comments below and we'll address them too!
