Monitoring Hana Database Performance: Key Strategies For Success

SAP HANA is a column-oriented database that stores data in its memory, allowing it to process and query large amounts of data with near-zero latency. This unique architecture enables advanced, real-time analytics. However, the HANA database can encounter performance issues such as inefficient query design, suboptimal system configurations, hardware limitations, and inadequate memory allocation. To ensure optimal performance, it is crucial to monitor the database's health and performance. This includes tracking key metrics such as CPU usage, memory consumption, disk utilisation, user connections, transactions, and expensive statements. By staying ahead of issues and bottlenecks, administrators can maintain the optimal performance of the HANA database and enhance the overall efficiency of the system.

Track CPU, memory and disk consumption

Tracking CPU, memory, and disk consumption is essential for maintaining optimal performance in the HANA database. Here are some detailed instructions on how to accomplish this:

CPU Monitoring:

• Utilize the System Monitor in HANA Studio: This tool provides an overview of your entire HANA system, allowing you to drill down into individual system details in the Administration Editor. It includes information about the data disk, log disk, trace disk, and alerts on resource usage with assigned priorities.
• Monitor CPU Usage Alerts: The HANA system will generate alerts when CPU usage reaches certain thresholds. For example, an alert with low priority will be raised at 80% CPU usage, while a high-priority alert will be triggered at 96%. Keep a close eye on these alerts to proactively manage CPU consumption.
• Analyze Query Execution Plans: Inefficient query design can lead to high CPU usage. Use tools like the SQL Plan Cache or the HANA Studio to identify queries or processes that are consuming excessive CPU resources. Optimizing these queries or redesigning them can help alleviate CPU bottlenecks.

Memory Monitoring:

• Track Memory Utilization: Use tools like the HANA Cockpit or HANA Studio to monitor memory usage across different components of the database. This includes tracking total memory usage, memory allocation for individual queries, and memory utilization by the host, column tables, row tables, code, and stack.
• Adjust Memory Allocation: If the database is running out of available memory, increase the memory allocation for the database or optimize memory-intensive queries. This can be done by adjusting the configuration settings to allocate more memory for data storage and processing.
• Perform Regular Maintenance: Regularly archive and purge data to free up memory. This helps keep the database lean and minimizes memory pressure, ensuring optimal performance.

Disk Consumption Monitoring:

• Monitor Disk I/O Latency: Utilize the HANA Cockpit or HANA Studio to track disk I/O latency. If high latency is detected, optimize the disk layout by distributing data across multiple disks using data striping.
• Ensure Disks Are Not Overloaded: Regularly monitor disk utilization and distribute the workload evenly across all available disks. This can be achieved through load balancing techniques or using storage technologies that automatically distribute the workload.
• Consider Faster Storage Devices: If optimizing the disk layout and distributing the workload do not resolve high disk I/O latency, consider upgrading to solid-state drives (SSDs) or other faster storage solutions.

By diligently monitoring and managing CPU, memory, and disk consumption, you can ensure the HANA database operates efficiently and effectively, providing optimal performance for your applications.

Monitor connections

Monitoring connections is an important aspect of maintaining the performance of your SAP HANA database. User connections to the SAP HANA server can indicate the load on the database server. If users complain about sluggish performance, administrators should check the user connections to the server to determine if the server is stressed due to an overload. This can be done using a HANA Connections Test, which reveals who is currently logged into the database server and provides a count of open, running, and idle connections for each login user.

When monitoring connections, administrators can identify users who are imposing the maximum load on the server and determine whether their connections are predominantly active or idle. The test also shows the maximum duration of a user connection, helping to identify idle connections that can be terminated to reduce the load on the server.

By tracking the number of active and idle connections, administrators can proactively manage server resources and ensure optimal performance for all users. This is especially important in a SAP HANA database, as idle connections can consume critical server resources and lock important database objects, hindering other users from accessing them.

In addition to the HANA Connections Test, there are monitoring tools available, such as Site24x7's SAP HANA monitoring plugin, which provides insights into various metrics, including the number of idle connections and the total number of connections currently queued. This information can help administrators make informed decisions about managing connections and optimising the performance of the SAP HANA database.

Ensure critical services are running

To ensure critical services are running, you should monitor the performance of your SAP HANA database. SAP HANA is a column-oriented database that stores data in its memory, allowing it to process and query large amounts of data with near-zero latency.

One way to monitor the performance of your SAP HANA database is to use a plugin integration, such as Site24x7's SAP HANA monitoring plugin. This plugin allows you to track key metrics and gain insights into the health of your database. With this plugin, you can monitor metrics such as active threads, active transactions, CPU usage, free physical memory, and more.

Another tool you can use is the System Monitor in HANA Studio, which provides an overview of your entire HANA system. From the System Monitor, you can drill down into the details of individual systems in the Administration Editor, which provides information about data disks, log disks, trace disks, and alerts on resource usage with priority.

Additionally, you can use the SAP HANA Cockpit for system monitoring and alerts. The SAP HANA Cockpit can be accessed directly from HANA Studio or via a web browser. To access the SAP HANA Database Administrator catalog, you should have the sap.hana.admin.roles::Monitoring or sap.hana.admin.roles::Administrator role assigned.

By monitoring these critical services, you can ensure that your SAP HANA database is running optimally and take corrective action if any issues arise.

Monitor transactions, cache and system alerts

Monitoring transactions, cache, and system alerts is crucial for maintaining optimal performance in the HANA database. Here are some detailed steps and strategies to effectively monitor these aspects:

Monitoring Transactions

Understanding the transactions executed in the SAP HANA database is essential. This includes tracking active and inactive transactions, as well as any transactions that are being aborted. By using tools like the Applications Manager, you can gain insights into transaction details and identify any blocked transactions. Additionally, reviewing SAP HANA system alerts during specific time periods can help in troubleshooting and performance optimisation.

Monitoring Cache

Effective cache utilisation is vital for optimal database performance. It's important to determine if the server is utilising its cache efficiently or if resizing the cache is necessary to ensure effective usage. The HANA Cockpit or HANA Studio tools can provide detailed insights into memory consumption, allowing administrators to make informed decisions about cache management.

Monitoring System Alerts

System alerts are crucial for proactive performance management. By setting up the Database Alert Monitor, administrators can identify memory problems early on, check database security, and monitor overall performance. This tool provides colour-coded displays, with green indicating that the database state is okay, and yellow or red indicating that production operation is at risk, requiring immediate administrative attention. Additionally, the Alert Inbox feature in System Monitoring helps manage alerts by creating and handling alerts when thresholds are violated.

General Performance Monitoring Strategies

In addition to monitoring transactions, cache, and system alerts, here are some general strategies to optimise HANA database performance:

• Query optimisation: SAP Basis professionals should focus on query optimisation techniques, including appropriate index usage, minimising wildcard characters, and avoiding unnecessary joins and subqueries.
• Memory allocation: Monitoring memory usage and adjusting settings accordingly is crucial, as HANA relies heavily on in-memory computing.
• Hardware considerations: Ensuring sufficient processing power, fast disk speeds, and adequate network bandwidth is essential for supporting the workload efficiently.
• System configurations: Regularly reviewing and optimising system configurations, including buffer settings, parallel processing parameters, and workload distribution across nodes, helps prevent performance bottlenecks.
• Data modelling and maintenance: Inefficient data modelling and outdated statistics can impact performance. Regular data archiving and purging are necessary to free up memory and maintain optimal performance.

Test the workload and expensive statements

Testing the workload and monitoring expensive statements are crucial aspects of optimising the performance of a HANA database. Here are some detailed steps and considerations to help you through this process:

Understanding Workload and Expensive Statements:

• Workload refers to the demands placed on the HANA server by various tasks and queries. Expensive statements are individual SQL queries that exceed a configured threshold in execution time. These statements can drain resources and block the execution of other SQL statements, degrading the performance of the database server.
• By default, the threshold for an expensive statement is set at 1 second (1,000,000 microseconds). However, you can configure this threshold based on your specific requirements.

Identifying and Isolating Expensive Statements:

• Utilise tools such as the HANA Expensive Statements Test to identify and isolate long-running SQL statements. This test will provide details such as the count of expensive SQL statements and the maximum duration of their execution.
• Additionally, you can use the detailed diagnosis feature of the test to identify the specific queries that are causing high load on the server.

Optimising Expensive Statements:

• Once you have identified the expensive statements, the next step is to optimise them for better performance. This can be achieved by fine-tuning the queries, improving their structure, or adding indexes to the database tables.
• By optimising these statements, you can reduce their execution time, thereby improving the overall performance of the database server.

Monitoring Tools and Techniques:

• SAP HANA provides various monitoring tools, such as the Applications Manager, which can help you track crucial metrics like response times, resource utilisation, and transaction performance. These tools provide real-time alerts, allowing you to take immediate corrective actions.
• Additionally, you can use SQL to identify the top SQL statements in terms of elapsed time, CPU consumption, or memory consumption.

Permanent Activation of Expensive Statements Trace:

It is recommended to permanently activate the Expensive Statements Trace as it has minimal performance overhead and provides significant analysis value. This allows for continuous monitoring and optimisation of the database's performance.

In conclusion, by understanding the workload and effectively monitoring and optimising expensive statements, you can enhance the performance of your HANA database. This process involves using the right tools, fine-tuning queries, and making informed decisions based on the data provided by the monitoring tools.

Frequently asked questions