SAP HANA Troubleshooting and Performance Analysis Guide

1 PUBLIC. SAP HANA Platform SPS 04. Document Version: 2019-10-31. SAP HANA Troubleshooting and Performance Analysis Guide 2019 SAP SE or an SAP affiliate company. All rights reserved. THE BEST RUN. Content 1 SAP HANA Troubleshooting and Performance Analysis Guide .. 6. 2 Analyzing Generic Symptoms..9. Performance and High Resource Utilization..9. Common Symptoms and Troubleshooting .. 11. Slow System-wide Performance .. 12. Slow Individual SQL Statements.. 15. Frequent Out of Memory (OOM).. 18. 3 Root Causes and Solutions.. 22. Memory Problems.. 22. Memory Information in SAP HANA Cockpit.. 23. Memory Information from Logs and Traces.

2 23. Memory Information from SQL Commands.. 25. Memory Information from Other Tools.. 29. Root Causes of Memory Problems.. 30. Transparent Huge Pages on Linux.. 38. CPU Related Root Causes and Solutions.. 39. Indicators of CPU Related Issues.. 39. Analysis of CPU Related Issues.. 39. Resolving CPU Related Issues.. 41. Retrospective Analysis of CPU Related Issues.. 41. Controlling Parallel Execution of SQL Statements ..42. Controlling CPU Consumption.. 44. Disk Related Root Causes and Solutions.. 48. Reclaiming Disk Space.. 52. Analyze and Resolve Internal Disk-Full Event (Alert 30).. 53. I/O Related Root Causes and Solutions.

3 55. Analyzing I/O Throughput and Latency.. 58. Savepoint Performance .. 59. Configuration Parameter Issues.. 61. Issues with Configuration Parameter log_mode (Alert 32 and 33)..63. Backup And Recovery.. 64. Delta Merge.. 68. Inactive Delta Merge.. 68. Indicator for Large Delta Storage of Column Store Tables..70. Failed Delta Merge.. 73. SAP HANA Troubleshooting and Performance Analysis Guide 2 PUBLIC Content Delta Storage Optimization.. 74. SAP Web IDE.. 75. Post-Installation Problems with Web IDE.. 75. Web IDE Common Issues with Modeling.. 77. Troubleshooting BW on HANA.. 79. Troubleshooting Multi-Dimensional Services Queries.

4 90. Troubleshooting Tips for the Calculation Engine..94. Native HANA Models.. 97. License Issues.. 97. System Locked Due to Missing, Expired, or Invalid License..98. License Problem Identification and Analysis .. 98. Resolution of License Issues.. 99. Security-Related Issues.. 100. Troubleshooting Authorization Problems.. 101. Troubleshooting Problems with User Name/Password Authentication..106. Troubleshooting Problems with User Authentication and SSO.. 108. Transactional Problems..111. Blocked Transactions .. 111. Troubleshooting Blocked Transaction Issues that Occurred in the Past.. 116. Multiversion Concurrency Control (MVCC) Issues.

5 116. Version Garbage Collection Issues.. 119. Statement Performance Analysis .. 121. SQL Statement Optimization..122. Analysis of Critical SQL Statements.. 125. Optimization of Critical SQL Statements.. 131. Application Performance Analysis ..139. SQL Trace Analysis .. 139. Statement Measurement.. 140. Data Analysis .. 141. Source Analysis .. 142. Technical Analysis .. 143. System Hanging Situations.. 144. Transparent Huge Pages.. 145. CPU Power Saving.. 146. Troubleshoot System Replication.. 147. Replication Performance Problems.. 148. Setup and Initial Configuration Problems.. 152. Intermittent Connectivity Problems.

6 157. LogReplay: Managing the Size of the Log File..158. Network Performance and Connectivity Problems.. 161. Network Performance Analysis on Transactional Level.. 161. SAP HANA Troubleshooting and Performance Analysis Guide Content PUBLIC 3. Stress Test with NIPING.. 163. Application and Database Connectivity Analysis .. 164. SAP HANA System Replication Communication Problems.. 166. SAP HANA Inter-Node Communication Problems.. 168. SAP HANA Dynamic Tiering.. 170. Tools and Tracing.. 170. Query Plan Analysis .. 170. Data Loading Performance .. 172. 4 Tools and Tracing.. 174. System Performance Analysis .

7 174. Thread Monitoring.. 174. Blocked Transaction Monitoring.. 177. Session Monitoring.. 178. Job Progress Monitoring.. 179. Load Monitoring..180. SQL Statement Analysis .. 180. Analyzing SQL Traces.. 181. Analyzing Expensive Statements Traces.. 185. Analyzing SQL Execution with the SQL Plan Cache.. 189. Query Plan Analysis ..190. Analyzing SQL Execution with the Plan Explanation.. 191. Analyzing SQL Execution with the Plan Visualizer.. 195. Result Cache.. 210. Static Result Cache..211. Dynamic Result Cache.. 216. Tracing for Calculation View Queries.. 223. Advanced Analysis .. 224. Analyzing Column Searches (qo trace).

8 224. Analyzing Table Joins.. 226. SQL Plan Stability.. 227. Additional Analysis Tools for Support.. 231. Performance Trace.. 231. Kernel Profiler.. 233. Diagnosis Information.. 234. Analysis Tools in SAP HANA Web-based Developer Workbench.. 235. 5 SAP HANA Database monitoring with Solution Manager.. 238. 6 Alerts and the Statistics Service.. 242. Reference: Alerts.. 243. Alerts Reference.. 262. SAP HANA Troubleshooting and Performance Analysis Guide 4 PUBLIC Content 7 Important Disclaimer for Features in SAP HANA.. 295. SAP HANA Troubleshooting and Performance Analysis Guide Content PUBLIC 5. 1 SAP HANA Troubleshooting and Performance Analysis Guide With SAP HANA, you can analyze data at incredible speeds, for example, with scans of 1 billion rows per second per core and join Performance of 10 million rows per second.

9 However, such results are only possible if the system is monitored and Performance issues are kept to a minimum. This Guide describes the measures you can take to identify and resolve specific Performance issues and shows you how to enhance the Performance of your SAP HANA database in the following areas: Host resources (CPU, memory, disk). Size and growth of data structures Transactional problems SQL statement Performance Security, authorization, and licensing Configuration. Prerequisites This Guide assumes knowledge of the relevant functionality of the SAP HANA database (knowledge which can be gained from HANA training courses such as HA100, HA200).

10 Access to the administration tool SAP HANA cockpit (or alternatively SAP HANA studio) is required. Overview Analyzing Generic Symptoms This section of the Troubleshooting Guide helps you to find out about causes of generic problems such as: slow system-wide Performance slow individual SQL statements frequent out-of-memory (OOM) situations Furthermore, you are directed to sections of this Guide that contain more specific root causes. SAP Note references lead you to possible solutions. Analyzing Generic Symptoms [page 9]. Root Causes & Solutions In this section of the Troubleshooting Guide you find concrete root causes for problems together with possible solutions.