We all know that the use or purpose of the functionality transactions in a SAP system is changing throughout the production system life from the initial implantation.
Therefore, I’m going to describe here just the result of teamwork between system administrators and developers which hopefully can become one of the best practices for the maintenance of the SAP ABAP system performance: The optimization of the database expensive access.
I must stress the importance of carrying out test systems which are daily refreshed with a mirror copy from the production system. Only under conditions like these, we can perform the traces over the test systems and won't cause any negative effects on the activity of the production system.
So, we have divided this operation in three steps or phases:
PHASE 1: In Workload Statistics of the production system you have to select: first, the most expensive transactions in Dialog Response Time for the months of the previous year. And within these, choose only those that have database performance problems in accordance to the following formula: Database Request Time > 40% of (Response Time – Wait Time).
PHASE 2: For each one of these transactions - the ones selected in the previous phase- you have to choose the users who have obtained the highest response times when running these transactions.
PHASE 3: Perform some SQL traces to analyze the usual procedure of the users and transactions selected before.
Finally, this practice ends with a fourth phase that consists in solving one by one all of the expensive accesses found before. In this phase, the most important thing is really to understand the meaning of queries to tables. So, I think it is a good tip and trick that you try to translate the problem to the mathematical logic and in this abstract level create solutions through the design of indexes in those cases where it could be possible.
Picture Phase 1: View of the ST03N Workload Statistics – transactions with database performance problem.
Picture Phase 2: View of the ST03N Workload Statistics – users with highest response times.
Picture Phase 3: View of the ST05 SQL Traces – expensive access, indexes.
- SAP Managed Tags:
