SAP HANA 2.0 SPS 01 What’s New: System Administration – by the SAP HANA Academy
In the upcoming weeks, we will be posting new videos to the SAP HANA Academy to show new features and functionality introduced with SAP HANA 2.0 Support Package Stack (SPS) 01.
The topic of this blog is system administration.
For the previous versions of this blog, see
- SAP HANA 2.0 SPS 00 What’s New: Administration
- SAP HANA 1.0 SPS 12 What’s New: Administration
- SAP HANA 1.0 SPS 11 What’s New: Administration
- SAP HANA 1.0 SPS 10 What’s New: Administration
For the full SAP HANA 2.0 SPS 01 blog list, see
Multi-tenant Database Containers as standard operation mode
It is not a new feature but an old feature that received a promotion. Starting with SAP HANA 2.0 SPS 01, the standard and only operation mode for SAP HANA is the multi-tenant database container (MDC) mode.
If all your system management operations are already compatible with MDC, there is nothing to do. If not, you might have to make a couple of changes. For more information, see SAP HANA 2.0 SPS 01 What’s New: Installation and Update – by the SAP HANA Academy.
EOL Statistics Server (but not the service running embedded)
As a consequence, as of SPS 01, the statistics server as an autonomous operating system process no longer exists. In a tenant database, the statistics server always runs embedded in the indexserver (database engine) process.
Single-container systems that are still running the statistics server independently are automatically migrated to embedded after the upgrade.
The embedded statistics service was introduced with SAP HANA 1.0 SPS 09.
See SAP HANA Administration Guide – The Statistics Service.
Dynamic Result Cache
The result cache was introduced with SAP HANA 1.0 SPS 09 and extended to include virtual tables and linked databases (for use with Smart Data Access) in SAP HANA 2.0 SPS 00.
Calculation views, SQL views, and CDS views (table functions) can all benefit from using the cache to store aggregated values for frequently executed complex queries. This allows for a reduction of CPU consumption which, as a consequence, should increase overall system throughput.
Not all views are suitable for caching, however, and implementation should be carefully tested to make sure that the correct results are returned, that the cached views is actually used, and that indeed the CPU consumption is reduced. See SAP Note 2014148 – Guidelines for Using the Query Result Cache and SAP HANA Troubleshooting and Performance Analysis Guide – Resources for Testing the Result Cache.
To further enhance the usability of the result cache, SAP HANA 2.0 SPS 01 introduces the Dynamic Result Cache. With this dynamic cache the risk of querying stale data is eliminated; it will always return transactionally consistent data and guarantees up-to-date results by firstly incrementally updating the cache with delta records of newly changed data and then returning the query result from the cache.
The dynamic cache is enabled by default. There are several system parameters available to tweak the configuration:
indexserver.ini - [dynamic_result_cache] enabled=true # maximum memory allocation for all dynamic result cache entries (MB) total_size=10000 # memory budget for individual cache entries (MB) max_cache_entry_size=1000 # prevent garbage collection on view table (sec) version_garbage_collection_blocking_period=60
Using dynamic cache is easy. Just add the clause WITH | ADD DYNAMIC CACHE to your view definition:
CREATE VIEW <view_name> <...> WITH DYNAMIC CACHE; ALTER VIEW <view_name> ADD DYNAMIC CACHE;
There are two hints (on/off) available for testing and tuning:
DYNAMIC_RESULT_CACHE | NO_DYNAMIC_RESULT_CACHE DYNAMIC_RESULT_CACHE_IMPLICIT_MATCH
Two views are available to analyse the operation of the cache:
See SAP HANA Troubleshooting and Performance Analysis Guide – Dynamic Result Cache for benefits and limitations.
JSON Document Store
After the row store and column store, SAP HANA now also includes a DocStore. Not unlike MongoDB, the SAP HANA JSON Document Store enables the developer to store and manage JSON documents (artifacts) with support for native operations on JSON including filtering, aggregation, and joining JSON documents with HANA relational tables. We are not talking about storing JSON as CLOB in a regular database column, in other words – the quick and dirty way.
The Document Store is an optional feature of the SAP HANA database and you have to enable the docstore operating system process per tenant database. This is identical to enabling scriptservers for the Application Function Library (AFL), for example:
ALTER DATABASE <database name> ADD 'docstore'
The DocStore does not have a pre-determined SQL port, all communication is routed through the regular indexserver (database engine) process.
After this, you can hand it off to the developer, see SAP HANA Developer Guide – Maintaining JSON Collections in the SAP HANA Document Store.
SAP HANA 2.0 SPS 00 introduced the Admission Control feature to set processing limits and to decide how to handle new requests if the system is close to the point of saturation. Two new parameters (queue_wait_time_threshold and record_limit) have been added to further fine tune this functionality. See SAP HANA Administration Guide – Configuring Admission Control.
Additionally, a new monitoring view has been added (M_ADMISSION_CONTROL_EVENTS), which you can query to get information about why a particular statement was queued.
Additionally, a workload class hint has been introduced so that administrators can apply an alternative workload class at the time when the query is executed.
SELECT name, MAX(salary) FROM employees GROUP BY name WITH HINT( WORKLOAD_CLASS("CEO") );
See SAP HANA Administration Guide – Managing Workload with Workload Classes.
SAP HANA Database Interactive Terminal (hdbsql)
Now we are getting to the small print, but another formatting option has been added to hdbsql: -fn.
With this option, line numbers are added when the input is displayed, making debugging easier.
See SAP HANA Administration Guide – SAP HANA HDBSQL Options.
On the SAP HANA Academy, there is a full playlist covering all aspects of administration: bit.ly/SAPHANAAdmin
For more information, see:
SAP HANA Blogs
- Multi-container database mode is the new default
- SAP HANA Admin and IT Cockpit for Operations and Monitoring
- SAP HANA workload management
SAP Help Portal
- What’s New in the SAP HANA Platform 2.0 (Release Notes) – SAP HANA System Administration (New and Changed)
- SAP HANA Troubleshooting and Performance Analysis Guide – Dynamic Result Cache
- SAP HANA Administration Guide – Results Caching for Virtual Tables and Linked Database
- SAP HANA SQL and System Views Reference – M_DYNAMIC_RESULT_CACHE_EXCLUSIONS System View
- SAP HANA Administration Guide – The Document Store
- SAP HANA Developer Guide – Maintaining JSON Collections in the SAP HANA Document Store
- SAP HANA SQL and System Views Reference – JSON Document Store Statement
- SAP HANA Administration Guide – Configuring Admission Control
- SAP HANA Administration Guide – Managing Workload with Workload Classes
- SAP HANA Administration Guide – SAP HANA HDBSQL Options
- SAP HANA Administration Guide – The Statistics Service
- 2404375 – SAP HANA Platform 2.0 SPS 01 Release Note
- 2422689 – SAP HANA 2.0 SPS 01 Database Revision 010
- 2433764 – SAP HANA cockpit 2.0 SP 02
- 2014148 – Guidelines for Using the Query Result Cache
Thank you for watching
The SAP HANA Academy provides technical enablement, implementation and adoption support for customers and partners with 1000’s of free tutorial videos.
For the full library, see SAP HANA Academy Library – by the SAP HANA Academy
For the full list of blogs, see Blog Posts – by the SAP HANA Academy
- Subscribe to our YouTube channel for updates
- Join us on LinkedIn: linkedin.com/in/saphanaacademy
- Follow us on Twitter: @saphanaacademy
- Google+: plus.google.com/+saphanaacademy
- Facebook: facebook.com/saphanaacademy