SAP Technical Architecture Manager | SAP AMS | SAP System Performance Optimization | Effective SAP Monitoring Setup for Maximizing SAP Availability | SAP Technical Architecture Continuous Improvements and Automation
BW system performance had suddenly deteriorated. Users were waiting response for their BW queries for hours. Dashboards were getting refreshed 3 times slower. Solution Manager jobs in BW kept running long. Heap memory errors were seen. We killed idle memory consuming processes. We increased heap memory allocation. Still, BW performance issues seemed to be occurring at random times. We kept cancelling the long running Solution Manager jobs in BW. BW queries were optimized. Standard performance parameter changes in SAP and Oracle were done (RFC load-balancing, Unused servers utilized for background processes, Oracle memlock parameter enablement etc.). That improved the situation to some extent. The solution manager jobs were pulling user change documents and consuming huge memory. We raised a ticket with SAP and were advised to archive user change documents as the corresponding tables were huge. However, we knew that the problem had started suddenly and not gradually. So, we kept analyzing which Solution Manager functionality needed so much of user change documents data. During the investigation we found that Configuration Validation was enabled. This data was being used for Change Analysis & Reporting. There were active extractors pulling this data for CCDB. Some of these extractors were for stores related to user/authorization changes. While going through the run-logs of those extractors, it was found that while usually the change documents to be checked were for a few users per day, it had risen exponentially on a recent date. It was found that the users had been loaded into SAP HR from where they had synced to BW. Thus the processes in BW, triggered by the solman extractor, would have become long running, while slowly consuming increasing amounts of memory on the servers on which it was triggered. The same sets of processes triggered on subsequent dates consumed more and more memory while running long. None of the processes could fully capture the change documents data for the high number of newly created users and continued running. This lead to the memory issues/performance issues for BW activities. Since we had started cancelling the processes, every time the process would be triggered by the solution manager extractor, it would start to collect data from the same date when it had failed for the first time, thus running into the same problem every time. We found SAP note 3378572 which, while it was for a different solution manager error, had the same underlying cause as ours: The extraction fails for a long time, which causes a wide date range read for config store AUTH_PROFILE_USER_CHANGE_DOC. The note advised to delete the store AUTH_PROFILE_USER_CHANGE_DOC so that it can get recreated automatically again and start extraction anew skipping the huge data from the recent dates. The SAPLSU_USER_CHANGE_DOC process was not long running anymore and did not affect memory thus finally bringing the investigation to a closure.