I have previously blogged about SAP HANA NSE and the impact of using it with Pure Storage products. One of the key areas I believe differentiates NSE from other warm data techniques is the inclusion of a component to analyse and provide recommendations on if data should be set to use NSE , or not. This is called the NSE advisor which is included in SAP HANA SPS04 onwards and can be managed from SAP HANA Cockpit 2.0 SP 11 onwards.
The NSE advisor creates recommendations based on internal heuristics advising if objects should be made page loadable (reduce the amount of memory used) or column loadable (increased performance). The general rules are that small objects which are accessed frequently should be accessed in memory and large objects which are infrequently accessed should be kept on disk to reduce memory usage.
Below I will detail how to configure and use the NSE advisor to generate recommendations for indexes, partitions and tables.
Some prerequisites and behaviour to take note of:
- The user executing SQL commands for the NSE advisor must be granted the SAP_INTERNAL_HANA_SUPPORT role.
- SAP HANA 2.0 SPS04 and onwards is required to use NSE.
- (Updated April 2021) – NSE is supported on both Scale Up and Scale Out deployment types as of SAP HANA 2.0 SPS05 and later.
- In order to generate recommendations a workload must be run on the database for 6 hours or more.
- While the NSE advisor is running, performance degradation and increased memory use is expected.
Getting recommendations using SQL Commands
Step 1. Clear all cache entries in the Access Statistics Cache
This ensures that the sample data used to generate recommendations is accurate for the workload to be analysed. Run the following SQL Command
| ALTER SYSTEM CLEAR CACHE (‘cs_access_statistics’); |
Step 2. Enable the NSE advisor
The NSE advisor makes use of the access statistics cache (known as cs_access_statistics) , when it is enabled sample data will be collected and stored in it based on how often an object is accessed and what is done to it during operations.
| ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’,’system’) SET (‘cs_access_statistics’,’collection_enabled’) = ‘true’ WITH RECONFIGURE; |
Step 3. Adjust NSE advisor tuning parameters
In order to understand the tuning parameters used by NSE, I need to explain how recommendations are generated from the access statistics cache. The following propoerties are analysed :
- Scan Density – The objects access scan count and its memory size ratio.
- Hot object threshold – The minimum scan density for an object to be considered a hot object.
- Cold object threshold – The maximum scan density for an object to be considered a cold object.
- Object size threshold – The minimum object size to be considered for recommendations.
The NSE advisor will recommend the object is page loadable if scan density is less than the cold object threshold but more than the object size threshold.
Scan_Density < Cold Object Threshold and Object Memory Size > Object Size Threshold
Objects are recommended to be column loadable if the scan density is more than the hot object threshold.
Scan_Density > Hot Object Threshold
The heauristics can be changed to include different object access patterns for various workloads. In the event that the NSE advisor is not showing any recommendations , changing these parameters will allow for more objects to be considered. The following parameters can be set to change the way recommendations are created :
- Hot_object_threshold_rel
- Cold_object_threshold_rel
- Min_object_size
In order to change these parameters the following SQL commands can be used :
| ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘system’) SET (‘cs_nse_advisor’,’hot_object_threshold_rel’) = ’30’ WITH RECONFIGURE; |
| ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘system’) SET (‘cs_nse_advisor’,’cold_object_threshold_rel’) = ’30’ WITH RECONFIGURE; |
| ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘SYSTEM’) SET (‘cs_nse_advisor’,’min_object_size’) = ‘999’ WITH RECONFIGURE; |
Note that the above values are for demonstration purposes only.
Step 4. Run the workload for a minimum of 6 hours
Step 5. Get the NSE Advisor recommendations
Execute the following SQL Command to view the recommendations generated by the NSE advisor :
| SELECT * FROM SYS.M_DEV_CS_NSE_ADVISOR |
When recommendations are returned, they will advise on the schema, table name, partition and which load unit the table should be configured for.
Getting recommendations using SAP HANA Cockpit
Navigate to the database in question and select the “Recommendations” tile.
Selecting the “Recommendations” tile will bring up a menu, select the “Configure” link in the top right-hand corner.
This launches the configure recommendations menu, offering 4 tabs: General, Physical Design, SQL and General Recommendations.
Navigate to the Physical Design Tab, settings for the NSE Advisor are located in the “Update Load Unit Section”. Update these values to fine tune how recommendations are identified.
To enable the NSE advisor, use the “General Recommendations” Tab. Before enabling, ensure that the NSE Recommendations are cleared to remove any junk data. Then simply turn on the NSE advisor by flipping the switch.
After 6 hours, returning to the main Recommendations menu will show any recommendations from the NSE advisor (listed as “Update Load Unit”)
Selecting the recommendation will bring up a more detailed dialog showing which table, partition or index should have its load unit changed, and to what load unit it should be changed to.
To alter the load unit just follow the process as set out in this blog post
Disabling the SAP HANA NSE Advisor
When finished with the recommendations from the NSE advisor, it should be disabled and the cache cleared so as to not adversely affect performance and memory usage.
| ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’,’system’) SET (‘cs_access_statistics’,’collection_enabled’) = ‘false’ [with reconfigure] |
| ALTER SYSTEM CLEAR CACHE (‘cs_access_statistics’); |
How the NSE Advisor Uses the Statement Cache for Recommendations
SAP Note : 2799997 – FAQ: SAP HANA Native Storage Extension (NSE)
SAP HANA Administration Guide for SAP HANA Platform : SAP HANA Native Storage Extension
Bringing FlashArray and SAP HANA closer with Native Storage Extension
Photo by Joshua Sortino on Unsplash