HANA troubleshooting
## **SAP HANA Performance Troubleshooting – Commands & Tools**
SAP HANA performance troubleshooting requires a combination of **SQL commands, system views, and SAP tools** to diagnose issues like high CPU usage, slow queries, memory bottlenecks, and disk I/O problems. Below are the key **commands and tools** along with **scenarios** where they are useful.
---
## **1. Check Overall System Health**
### **Command:**
```sql
SELECT * FROM M_SYSTEM_OVERVIEW;
```
### **Scenario:**
🔹 **Issue:** General performance degradation across the system.
🔹 **Use Case:** Provides a high-level summary of the system's status, including memory, CPU, and disk utilization.
---
## **2. Check Active Sessions and Long-Running Queries**
### **Command:**
```sql
SELECT * FROM M_SESSION_CONTEXT;
```
```sql
SELECT * FROM M_ACTIVE_STATEMENTS WHERE ELAPSED_TIME > 5000 ORDER BY ELAPSED_TIME DESC;
```
### **Scenario:**
🔹 **Issue:** Some queries take too long to execute.
🔹 **Use Case:** Identifies active queries and long-running SQL statements affecting performance.
---
## **3. Analyze Expensive SQL Statements**
### **Command:**
```sql
SELECT TOP 10 * FROM M_EXPENSIVE_STATEMENTS ORDER BY RECORD_TIME DESC;
```
### **Scenario:**
🔹 **Issue:** High CPU usage caused by inefficient queries.
🔹 **Use Case:** Identifies SQL queries that consume excessive resources and need optimization.
---
## **4. Check Memory Usage**
### **Command:**
```sql
SELECT HOST, PORT, TOTAL_MEMORY_USED, TOTAL_MEMORY_ALLOCATED FROM M_HOST_RESOURCE_UTILIZATION;
```
```sql
SELECT * FROM M_MEMORY_CONSUMPTION;
```
### **Scenario:**
🔹 **Issue:** High memory usage leading to out-of-memory (OOM) errors.
🔹 **Use Case:** Helps identify memory-intensive components.
---
## **5. Identify High CPU Utilization**
### **Command:**
```sql
SELECT * FROM M_SERVICE_STATISTICS WHERE SERVICE_NAME = 'indexserver' ORDER BY CPU_TIME DESC;
```
```sql
SELECT * FROM M_LOAD_HISTORY WHERE TIME >= ADD_SECONDS(CURRENT_TIMESTAMP, -600);
```
### **Scenario:**
🔹 **Issue:** CPU spikes during peak hours.
🔹 **Use Case:** Identifies services consuming high CPU and helps detect bottlenecks.
---
## **6. Analyze Table Growth and Large Tables**
### **Command:**
```sql
SELECT TABLE_NAME, MEMORY_SIZE_IN_TOTAL, RECORD_COUNT FROM M_TABLE_PERSISTENCE_STATISTICS ORDER BY MEMORY_SIZE_IN_TOTAL DESC;
```
### **Scenario:**
🔹 **Issue:** Unoptimized tables consuming too much memory.
🔹 **Use Case:** Helps identify large tables that may need partitioning or indexing.
---
## **7. Check Disk I/O Bottlenecks**
### **Command:**
```sql
SELECT * FROM M_DISK_USAGE;
```
```sql
SELECT * FROM M_VOLUME_IO_TOTAL_STATISTICS ORDER BY TOTAL_READS + TOTAL_WRITES DESC;
```
### **Scenario:**
🔹 **Issue:** Slow disk I/O affecting database performance.
🔹 **Use Case:** Identifies tables or indexes causing high disk activity.
---
## **8. Identify Lock Contention Issues**
### **Command:**
```sql
SELECT * FROM M_TRANSACTIONS WHERE BLOCKED_MODE = 'BLOCKED';
```
```sql
SELECT * FROM M_LOCKS;
```
### **Scenario:**
🔹 **Issue:** One query blocking others, leading to deadlocks.
🔹 **Use Case:** Helps detect locks and transactions causing contention.
---
## **9. Detect High Network Latency**
### **Command:**
```sql
SELECT * FROM M_CONNECTIONS ORDER BY TOTAL_DATA_SENT + TOTAL_DATA_RECEIVED DESC;
```
### **Scenario:**
🔹 **Issue:** Performance degradation due to slow client connections.
🔹 **Use Case:** Analyzes network traffic and identifies high-latency clients.
---
## **10. Find Column Store Unloads (Evictions)**
### **Command:**
```sql
SELECT * FROM M_CS_UNLOADS;
```
### **Scenario:**
🔹 **Issue:** Frequent column unloads causing slow query performance.
🔹 **Use Case:** Identifies large objects being unloaded due to memory pressure.
---
# **SAP HANA Troubleshooting Tools**
| Tool | Usage |
|------|-------|
| **SAP HANA Studio** | Graphical interface for performance monitoring and SQL execution |
| **SAP HANA Cockpit** | Web-based tool for monitoring system health, resource utilization, and troubleshooting |
| **hdbsql (Command Line Client)** | Executes SQL commands for performance analysis |
| **SAP Early Watch Alert (EWA)** | Proactive system health monitoring |
| **SQL Plan Cache (M_SQL_PLAN_CACHE)** | Checks execution plans of queries |
| **Performance Trace (HDBAdmin & HDBStudio)** | Captures detailed runtime analysis of SQL queries |
---
# **Example Performance Troubleshooting Scenarios**
### **Scenario 1: Slow Query Execution**
✅ Run:
```sql
SELECT * FROM M_ACTIVE_STATEMENTS WHERE ELAPSED_TIME > 5000 ORDER BY ELAPSED_TIME DESC;
```
✅ Use `EXPLAIN PLAN` to analyze execution:
```sql
EXPLAIN PLAN FOR ;
SELECT * FROM EXPLAIN_PLAN_TABLE;
```
✅ Check if indexing or partitioning can optimize the query.
---
### **Scenario 2: High CPU Utilization**
✅ Run:
```sql
SELECT * FROM M_SERVICE_STATISTICS WHERE SERVICE_NAME = 'indexserver' ORDER BY CPU_TIME DESC;
```
✅ Check expensive SQL statements:
```sql
SELECT * FROM M_EXPENSIVE_STATEMENTS ORDER BY RECORD_TIME DESC;
```
✅ Optimize expensive queries and reduce CPU-intensive joins.
---
### **Scenario 3: High Memory Consumption & Frequent Unloads**
✅ Run:
```sql
SELECT * FROM M_CS_UNLOADS;
SELECT * FROM M_MEMORY_CONSUMPTION;
```
✅ Identify large tables:
```sql
SELECT TABLE_NAME, MEMORY_SIZE_IN_TOTAL FROM M_TABLE_PERSISTENCE_STATISTICS ORDER BY MEMORY_SIZE_IN_TOTAL DESC;
```
✅ Increase HANA memory allocation or tune table partitions.
---
### **Scenario 4: Lock Contention (Blocked Transactions)**
✅ Check blocked transactions:
```sql
SELECT * FROM M_TRANSACTIONS WHERE BLOCKED_MODE = 'BLOCKED';
SELECT * FROM M_LOCKS;
```
✅ Identify the blocking transaction and terminate if necessary.
---
### **Scenario 5: Disk I/O Bottleneck**
✅ Run:
```sql
SELECT * FROM M_VOLUME_IO_TOTAL_STATISTICS ORDER BY TOTAL_READS + TOTAL_WRITES DESC;
```
✅ Check disk space:
```sql
SELECT * FROM M_DISK_USAGE;
```
✅ Move large tables to **hot/cold storage** and optimize indexing.
---
# **Conclusion**
🔹 Use **SQL commands** to identify bottlenecks in CPU, memory, disk, and network.
🔹 Use **SAP HANA Studio, Cockpit, and hdbsql** for deep analysis.
🔹 Apply **indexing, partitioning, and memory optimization** to resolve issues.
Would you like help with any specific issue in your HANA database? 🚀
Comments
Post a Comment