Unlock the Power of SAP HANA Performance Optimization: A Comprehensive Guide for SAP Basis Administrators [Part 2 : Deep Dive ]

In SAP systems, the performance of the underlying database plays a crucial role in delivering a seamless user experience. SAP HANA, being an in-memory database, offers exceptional speed and agility. However, it's essential to monitor and troubleshoot potential performance bottlenecks to ensure optimal system performance. In this blog post, we will explore key areas to focus on when monitoring the SAP HANA database and provide examples of how to identify and address common performance issues.

Threads:

1. Threads in the SAP HANA database represent individual tasks that execute concurrently. Monitoring thread utilization helps identify any thread-related issues, such as high thread utilization or thread exhaustion. You can use the M_SERVICE_THREADS table to gather information about active threads and their status. For example, a high number of waiting threads may indicate resource contention or blocking situations that require investigation and optimization
   
   

1. To retrieve  thread based on SQL queries : SELECT * FROM M_SERVICE_THREADS WHERE SQL_STATEMENT_ID = '<SQL_STATEMENT_ID>';
   
   

2. Obtain the transaction ID of the specific transaction you are interested in. You can find this information in the M_TRANSACTIONS table.
   SELECT STATEMENT_ID
   FROM M_SQL_PLAN_CACHE
   WHERE TRANSACTION_ID = '<YOUR_TRANSACTION_ID>';

3. Use that statement id to check the threads associated.

SELECT * FROM M_SERVICE_THREADS WHERE SQL_STATEMENT_ID = '<SQL_STATEMENT_ID>';
   

2. Sessions in the SAP HANA database represent connections established by users or applications. Monitoring sessions allows you to understand the system's workload and identify any potential performance issues related to session management. The M_SESSIONS table provides insights into active sessions, including user information, SQL statements being executed, and resource consumption. By analyzing session-related metrics, such as active sessions and response times, you can pinpoint potential areas of improvement.

• SELECT SESSION_ID, SQL_TRANSACTION_ID FROM M_SESSIONS WHERE    SESSION_ID = <session_id>;

• SELECT SESSION_ID FROM M_TRANSACTIONS WHERE TRANSACTION_ID = <transaction_id>;

3. Blocked transactions occur when one transaction is waiting for a resource held by another transaction. Monitoring blocked transactions helps identify concurrency issues and optimize transactional throughput. The M_LOCKS table provides information about locks and lock waits in the SAP HANA database. By analyzing the lock information, you can identify blocking situations and take appropriate actions to resolve them, such as optimizing transaction isolation levels or redesigning data access patterns.

• Query to select all object that needs to be acquired by A transaction 

SELECT DISTINCT OBJECT_SCHEMA, OBJECT_NAME FROM M_LOCKS WHERE LOCK_TYPE != 'Exclusive' AND OWNER_HOST = '<session_host_A>' AND OWNER_PORT = <session_port_A>;

• Query to select which session is holding a particular object say ‘YUT’

SELECT * FROM M_LOCKS WHERE LOCK_TYPE = 'Exclusive' AND OBJECT_SCHEMA = '<schema_name>' AND OBJECT_NAME = 'YUT';

4. The SQL Plan Cache in SAP HANA stores execution plans for SQL statements executed in the database. Monitoring the SQL Plan Cache allows you to analyze the efficiency of SQL execution and identify potential performance bottlenecks. The M_SQL_PLAN_CACHE table provides information about SQL execution plans, including plan reuse, plan hits, and plan evictions. By identifying inefficient or missing execution plans, you can optimize SQL statements and improve overall query performance.

• Query to select the statement id from M_TRANSACTIONS

SELECT DISTINCT STATEMENT_ID FROM M_TRANSACTIONS WHERE TRANSACTION_ID = '<transaction_id>'

• Query to check the exact plan there

SELECT * FROM M_SQL_PLAN_CACHE WHERE STATEMENT_ID = '<transaction_id>';

5. The Expensive Statement Trace feature in SAP HANA helps identify SQL statements consuming significant system resources. By enabling and analyzing the trace output, you can identify expensive queries and optimize their execution. The M_EXPENSIVE_STATEMENT_STATISTICS table provides details on the execution times and resource consumption of expensive SQL statements. By tuning or redesigning these statements, you can reduce resource consumption and improve overall system performance.

• SELECT TOP 10 * FROM M_EXPENSIVE_STATEMENT_STATISTICS ORDER BY EXECUTION_TIME DESC;

Sample output 

STATEMENT_ID | EXECUTION_TIME | CPU_TIME | WAIT_TIME | IO_TIME | PLAN_HASH_VALUE | PLAN_LAST_EXECUTED | PLAN_LAST_EXECUTION |

------------------------------------------------------------------------------------

1024         | 5634           | 4213     | 1421      | 0       | 98234           | 2023-05-15 10:23:56 | 2023-05-15 10:23:56 |

2156         | 4521           | 3258     | 1263      | 0       | 87915           | 2023-05-15 12:45:19 | 2023-05-15 12:45:19 |

9876         | 3987           | 2876     | 1111      | 0       | 76981           | 2023-05-15 15:32:42 | 2023-05-15 15:32:42 |

...

If you get the hash value associated here , you can use that to check in M_SQL_PLAN_CACHE ( information about the SQL execution plans ) and M_SQL_PLAN_CACHE_STATEMENTS (information about individual SQL statements in the SQL plan cache)

6. Monitoring job progress in the SAP HANA database helps ensure timely completion of critical tasks and identifies any potential issues impacting job performance. The M_JOB_PROGRESS table provides information about the progress and status of running jobs. By monitoring job progress, you can identify long-running or stuck jobs, analyze potential dependencies or resource constraints, and take corrective actions to ensure job completion within the desired timeframe.

Don’t Get confused here The background jobs in SAP HANA are defined and managed through the Job Scheduler framework, which includes the SYS.JOBS table. This table stores information about the background jobs, including their names, schedules, status, and other relevant details this is not the SAP BACKGROUND JOBS.

Scheduled backups in SAP HANA are not managed through the Job Scheduler framework or reflected in the SYS.JOBS table. The Job Scheduler is primarily responsible for executing scheduled tasks, but it does not handle the scheduling and execution of backup operations.

7. Monitoring the load on the SAP HANA database helps assess its capacity and identify any potential performance constraints. The M_SERVICE_MEMORY and M_HOST_RESOURCE_UTILIZATION tables provide insights into resource consumption and system load. By monitoring memory, CPU, and disk utilization, you can identify periods of high load and optimize resource allocation to ensure optimal performance.

• SELECT TOP 10 * FROM M_SERVICE_MEMORYORDER BY TOTAL_MEMORY_USED DESC;

• SELECT TOP 10 * FROM M_HOST_RESOURCE_UTILIZATION ORDER BY "TIME" DESC;

Conclusion:

Proactively monitoring and troubleshooting performance issues in the SAP HANA database is crucial for maintaining a high-performing SAP system. By focusing on threads, sessions, blocked transactions, SQL plan cache, expensive statements, job progress, and system load, you can identify potential bottlenecks and optimize performance. Implementing these monitoring techniques and taking appropriate actions will help ensure optimal system performance, enhance user experience, and maximize the benefits of SAP HANA's in-memory capabilities.