SAP HANA DB Performance Tunning.

[AbhijeetChaks]

SAP HANA is an in-memory, column-oriented relational database management system designed for high-speed transactions and real-time analytics. While SAP HANA is optimized for performance, administrators often need to fine-tune the database to ensure smooth operations, minimize latency, and maximize efficiency.

In this blog, we will explore best practices for SAP HANA database performance fine-tuning, covering memory management, query optimization, indexing strategies, and other crucial aspects.

Understanding SAP HANA Performance Architecture 
SAP HANA’s performance relies on its in-memory architecture, where data is stored in columnar format, enabling fast read operations and parallel processing. To optimize performance, it is essential to focus on :
  Memory utilization: Efficiently managing RAM allocation for persistence, caches, and temporary storage.
  CPU optimization: Ensuring workload distribution across available cores.
  Disk I/O management: Optimizing logs, backups, and persistent storage for durability.

- Key Performance Fine-Tuning Strategies

1.  Memory Management Optimization: Memory is one of the most critical resources in SAP HANA, and improper usage can lead to performance bottlenecks. To monitor memory usage/memory consumption (Use HANA studio/HANA Cockpit).
SELECT * FROM M_MEMORY;

Adjust Memory Allocation to Modify global_allocation_limit based on workload requirements
ALTER SYSTEM SET GLOBAL ALLOCATION LIMIT = <value>;

2. Query Optimization:  Query optimization is a critical aspect of SAP HANA performance tuning, ensuring that SQL queries execute efficiently with minimal resource consumptions. Poorly optimized queries can lead to high memory usage, COU overload and slow response times.
 2.a - Use SQL Plan Cache Analysis: SQL Plan Cache in SAP HANA stores execution plans for queries. Analyzing it helps identify inefficient queries and optimize performance. Regularly reviewing the SQL Plan cache helps ensure efficient query execution and optimal database performance. 
                                          

SQL - SELECT TOP 10 STATEMENT_STRING, EXECUTION_COUNT,     TOTAL_EXECUTION_TIME / EXECUTION_COUNT AS AVG_EXECUTION_TIME_MS,  TOTAL_MEMORY_USED / 1024 / 1024 AS MEMORY_MB FROM M_SQL_PLAN_CACHE ORDER BY AVG_EXECUTION_TIME_MS DESC;

SQL - SELECT * FROM M_SQL_PLAN_CACHE_OVERVIEW;
(For a broader view of expensive queries)

2.b - Analysis Expensive Queries: Expensive queries consume high CPU, memory and execution time, leading to performance bottlenecks in SAP HANA. Identify and optimizing these queries can significantly improve system efficiency. Optimization strategies like Refactor complex queries, Use Indexing, Optimization Joins and Partitions Large tables. 
              

SQL - SELECT TOP 10   START_TIME,  STATEMENT_STRING,  DURATION_MICROSEC / 1000000 AS DURATION_SEC,  MEMORY_SIZE / 1024 / 1024 AS MEMORY_MB,  CPU_TIME / 1000000 AS CPU_TIME_SEC FROM M_EXPENSIVE_STATEMENTS ORDER BY DURATION_MICROSEC DESC;

2.b.1 - Enable Expensive State: If we don’t see enough results in M_EXPENSIVE_STATEMENTS, enable expensive statement tracing.

SQL - ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('expensive_statement', threshold_duration') = '5000000' WITH RECONFIGURE;

2.c - Avoid SELECT ****(Fetch Only Required Columns): Using SELECT* in query retrieves all columns data from table, which can lead to high memory consumption, increased CPU load and slower query execution. Instead, specify only the required columns to improve performance. Like instead using SELECT * FROM customer; Use: SELECT XYZ_ID, NAME FROM customer.

2.d - Partitioning Large Tables in SAP: Partitioning is a technique used in SAP HANA to split large tables into smaller, more manageable data to improve query performance and optimize memory usage. When a table is partitioned, SAP HANA process queries more efficiently by scanning only relevant partitions instead of the entire table. Types of partitioning Hash Partitioning, Range Partitioning and Round-Robin Partitioning. 

Range Partitioning
SQL - CREATE COLUMN TABLE SALES (ID INT PRIMARY KEY, SALE_DATE DATE, AMOUNT DECIMAL(10,2))PARTITION BY RANGE (SALE_DATE) (PARTITION VALUE <= '2023-01-01', PARTITION VALUE <= '2024-01-01', PARTITION OTHERS);

HASH Partitioning
SQL - CREATE COLUMN TABLE CUSTOMER_ORDERS (ORDER_ID INT PRIMARY KEY, CUSTOMER_ID INT,  ORDER_AMOUNT DECIMAL(10,2))PARTITION BY HASH (ORDER_ID) PARTITIONS 4;

SELECT TABLE_NAME, PARTITION_TYPE, PARTITION_COUNT FROM M_TABLE_PARTITIONS WHERE TABLE_NAME = 'SALES';
                              (Verify Partitioning run above query to check how the table is partitioned)

3. SAP HANA Indexing Strategies: Indexing in SAP HANA helps improve query performance by reducing the amount of data scanned. However, since HANA is an in-memory database optimized for columnar storage, excessive indexing can increase memory usage without significant performance benefits.

3.a - Use Primary and Unique Indexes effectively: SAP HANA automatically creates primary key index when define a primary key. It ensures that uniqueness and speed up lookup but may slow down inserts. Example creating a Primary index

SQL - CREATE TABLE Customers (CustomerID INT PRIMARY KEY, NAME NVARCHAR(50), CITY NVARCHAR(50));

3.b - Use Unique Indexes: Similar to primary key but allow null values. Use when need uniqueness but don’t want it as the primary key. Example creating a Unique Index

SQL – CREATE UNIQUE INDEX UQ_Email ON CUSTOMERS(EMAILS);

1. c – Avoid Unnecessary Secondary Indexes: SAP HANA columnar storage already provide efficient indexing for most queries. Create Secondary indexes only when necessary, such as for frequently filtered queries on non-primary key columns.

 3.d – Monitor and Drop Unused Index: Monitoring and cleaning up unused indexes, SAP HANA performance and memory efficiency can be optimized. Avoid keeping unnecessary indexes that increase memory consumption. Identify unused indexes, check if indexes are rarely or never used to save memory.

SQL: SELECT TABLE_NAME, INDEX_NAME, INDEX_TYPE, RECORD_COUNT FROM M_INDEXES WHERE RECORD_COUNT = 0;

Drop unused index SQL - DROP INDEX IDX_CITY;

3.e – Optimize Joins with Indexes: To improve joins performance in SAP HANA, indexing foreign key columns is essential. Creating index on frequently joined columns reduces full table scans and speeds up query execution. Additionally, ensuring primary or unique indexes exist on joined columns enhances efficiency. However, over indexing should be avoided as it can slow down insert and update operations. Indexing frequently used joins leads to faster query execution and better memory management in database.  

3.d – Partition Large Indexes for Better Performance: Partitioning large tables helps SAP HANA distribute data across multiple processing units, improving query performance and memory management. When a table has a massive dataset, indexing alone may not be sufficient. By partitioning the index, queries can process data in parallel, reducing execution time. Two very common types of partitioning for large Indexes. 1) Hash Partitioning, 2) Range Partitioning.

SQL - SELECT TABLE_NAME, INDEX_NAME, RECORD_COUNT, MEMORY_SIZE_IN_TOTAL / 1024 / 1024 AS MEMORY_MB FROM M_INDEXES WHERE MEMORY_SIZE_IN_TOTAL > 1024 * 1024 * 1024  -- Filters indexes larger than 1GB
ORDER BY MEMORY_MB DESC;
                                                            (To check the size of index)

4. Parallel Processing and Thread Optimization: SAP HANA uses parallel processing to maximize CPU utilization and improve query performance. It distributes workload across multiple threads, leveraging multiple core CPUs and columnar storage for efficient execution.

              - Parallel Query Execution Distributes query execution across multiple threads.
              - Parallel Index scans improve performance when scanning large indexes datasets.
              - Parallel Background tasks include data loads, replication and garbage collection.
              - Parallel table processing, each partition is processed by different threads for efficiency.             

4.a – Checking thread usage & Parallel execution to analysis SAP HANA parallel processing usage, use the below query
              SQL - SELECT * FROM M_SERVICE_THREADS WHERE STATE!=’idle’;

4.b - Check CPU & Thread Utilization by SQL Statements. Identifies queries consuming high CPU and using multiple threads.

SQL - SELECT STATEMENT_STRING, THREAD_COUNT, CPU_TIME, EXECUTION_COUNT FROM M_SQL_PLAN_CACHE ORDER BY CPU_TIME DESC LIMIT 10;

4.c – Enable parallel execution for queries and ensure parallel query execution is thread on.

SQL- ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('parallel_execution', 'enabled') = 'true' WITH RECONFIGURE;

4.d – Increase maximum parallel threads per query. Default is 5, increasing can improve performance for complex workloads

SQL- ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('execution', 'max_concurrent_statements') = '10' WITH RECONFIGURE;

4.e – Optimize memory allocation for threads to adjust the number of threads per service. It helps allocate optimal threads for complex workload.

SQL- ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('threads', 'max_concurrency') = 'value' WITH RECONFIGURE;

4.f - Monitoring parallel performance to identify long running queries that may need parallel tuning. To check parallel execution efficiency run the following query.

              SQL- SELECT*FROM M_EXPENSIVE_STATEMENTS ORDER BY DURATION_MICROSEC DESC;

5. Disk I/O Optimization: SAP HANA is an in-memory database, but it still relies on disk storage for logging, persistence and backup. Optimizing disk I/O is critical to ensure high performance, reduce latency and avoid bottlenecks. The primary goal of disk I/O optimization is to maximize throughput and minimize latency by optimizing storage configuration, filesystem settings and system parameters.

5.1 – Linux Kernel & I/O schedulers tunning: Use the noop or deadline I/O scheduler for better disk performance.
                 echo noop > /sys/block/sdX/queue/scheduler
-- Adjust kernel parameters in /etc/sysctl.conf
                  vm.dirty_background_ratio=5
                  vm.dirty_ratio=5
                  vm.swappiness=0
                  vm.overcommit_memory=2

5.b – Optimizing SAP HANA Log Volume Size: The log volume is critical because it stores all transactions logs before they are written to the data volume. To optimize log volume performance
              - Use SSD for log volumes to ensure high throughput and low latency
              - Avoid using shared storage unless it is high-performance with low latency.
              - Keep the log volume separately from data volumes to prevent I/O contention.
              - SAP recommends a log volume size of at least 4 times of log buffer size, if you frequently see log segment switches due to space constraints, increase the log volume size

5.c – Adjusting log buffer size for better performance: The log buffer temporarily stores transaction logs before writing them to the log volume. Optimizing can reduce the number of I/O operations. Restart SAP HANA database services for changes to take effect.

              SQL- SELECT VALUE FROM M_INIFILE_CONTENTS WHERE KEY =’logbuffer_size’;

• Increase log buffer size based on requirement. Default value is 32 MB and SAP recommended 64 MB or 128 MB depending on transaction workload. To modify

SQL – ALTER SYSTEM ALTER CONFIGURATION (‘global.ini’, ‘SYSTEM’) SET (‘persistence’, ‘logbuffer_size’) = ‘128MB’ WITH RECONFIGURE;  

- Note: If WAIT_TIME is high, increase logbuffer_size and check disk performance.

5.d - Additional Performance Tunning
              - Enable Asynchronous I/O for log writing (enable_async_io = on)
              - Ensure fast log writes, if log_write_duration is high, optimize storage or increase log buffer.
              SQL -  SELECT*FROM M_LOG_WRITTING_STATISTICS;

Thanks,
Abhijeet Chakraborty

[Pk39]

Dear sir,

Thanks for the detailed informative blog. 

I want to add one delta merge point which impact the performace of HANA .

To keep control of the delta merge we keep our table enable AUTOMERGE but in the data loading time we keep it into disable auto merge and enable smart merge for large volume tables.

Auto Merge :  HANA keeps a compressed “main” store of column tables and an uncompressed “delta” store, for new items. Periodically, a process called mergedog combines the two in a DELTA MERGE process. Sometimes however your tables may not be configured to automerge (you can issue ALTER TABLE table name ENABLE AUTOMERGE), and occasionally merged fails and you have to restart HANA. This can cause the delta store to become very large, and because it is uncompressed, this is a big issue if you don’t have much RAM.

**---------------------------------------------------------------

ALTER TABLE “SCHEMA”.”TABLE” DISABLE AUTOMERGE;

IMPORT FROM ‘/sapmnt/log/test.ctl’ WITH THREADS 120 BATCH 200000;

MERGE DELTA OF “SCHEMA”.”TABLE”;

ALTER TABLE “SCHEMA”.”TABLE ENABLE AUTOMERGE;

**---------------------------------------------------------------

 Thanks & Regards,

Abhishek Chaudhary