What’s new in SAP HANA Dynamic Tiering 2.0 Support Package 03? Quite a lot, actually. This blog highlights the latest enhancements.
Query Performance Improvements
The SP 03 release features several significant query performance improvements.
Zone maps help range type queries run faster. They are automatically created when you add a new column to an extended or multistore table. For details, see Enhanced Query Performance.
Dynamic Partition Pruning
For HANA columns and multistore tables with time-selection partitioning, dynamic partition pruning happens when you execute a query. In dynamic partition pruning, the SQL optimizer tries to eliminate any partition from the selection that is not essential for the query to be executed. The decision about which partitions to load is based on precalculated column statistics of the data in selected columns. This means that you have to run CREATE STATISTICS for a specified table, partition, and column before you execute queries. See Static and Dynamic Partition Pruning.
Intermediate Results Caching
Dynamic tiering now pulls and caches data from in-memory HANA tables during cross-store JOIN operations. This behavior is enabled by default. Subsequent query requests can reuse the cache if the cached query includes the subset of columns and predicates. See Intermediate Results Caching.
Heterogeneous Multistore Tables
Dynamic tiering also supports heteroneneous multistore tables now. This is useful for tables that don’t have evenly distributed data, and have empty partitions. This features lets you partition by range on two columns, and the columns do not need to be part of the primary key. The partitions may reside in either default or extended storage. See Multistore Table Partition Types.
Backup and Restore Improvements
There are two major new features for backup and restore.
BACKINT Performance Improvements
Like SAP HANA, dynamic tiering now supports multiple channels for writing data and log backups to third party tools through the BACKINT API. See Backups with Third-Party Backup Tools.
Support for log_mode=overwrite
On test and development systems that don’t need to do point-in-time recovery (PITR) of backups, you can use the new log_mode=overwrite setting to save storage space. After you set log_mode=overwrite, dynamic tiering logs are not backed up and PITR is no longer possible. No logs are created on the disk separate from the dynamic tiering store. Logs are created on disk for the dynamic tiering metadata store (ES_SYSTEM dbspace), then removed on checkpoint. See Disabling Point-in-Time Recovery by Setting Log Mode Overwrite.
New Diagnostic System Views
Five new system views improve diagnostics for dynamic tiering data.
This view shows where time and resources are spent when you execute a statement that involves extended storage. See M_ES_EXPENSIVE_STATEMENTS System View [Dynamic Tiering].
The M_REMOTE_STATEMENTS view has a new column, STATEMENT_ID. You can join M_REMOTE_STATEMENTS with other monitoring views such as M_PREPARED_STATEMENTS on STATEMENT_ID. Joining different views by STATEMENT_ID helps you analyze remote activity of any given HANA statement. See M_REMOTE_STATEMENTS System View.
Now you can see when, and how often, a table partition was selected. This view helps you balance loads in clusters and helps you decide when to move partitions to dynamic tiering or extension nodes. The view is only for column store partitions in default storage (HANA memory partitions. See M_TABLE_PARTITION_STATISTICS System View.
M_TABLE_PARTITIONS shows information on memory and disk usage for partitioned tables. The view is available for HANA column partitioned tables and has been extended to provide information for multistore tables. See M_TABLE_PARTITIONS System View.
Use M_TABLE_PRUNING_STATISTICS for insight into access of dynamic tiering data, with reflection on pruning effectiveness. It helps you detect unexpectedly heavy processing of data in dynamic tiering. See M_TABLE_PRUNING_STATISTICS System View.