SAP Monitoring Long-running Transaction and Expensive SQL Analysis

  

Are you aware that the lack of monitoring of long-running jobs can cause performance issues, impacting online users as well as other workloads? If your SAP system is experiencing performance degradation, it may be due to the long-running job execution time that is caused by an expensive SQL statement. Other causes and side effects of long-running transactions can also show up in areas like waits, blocking, locks, memory bottlenecks, high process, and CPU utilization, and timeouts.

When performing root cause analysis for long-running jobs, you will miss a major piece of the puzzle if you only get notified when a job exceeded the expected runtime.

There are other several reasons that may cause the slow execution of a job, such as general system problems, poorly designed ABAP code, application or system workload, database access.

However, having an insight into whether or not the source of the performance problem is hiding in the SQL statement is important to enabling support to move on quickly with root cause analysis and fixing the issue. Thus, the root cause analysis (RCA) must include the monitoring of potentially expensive SQL statements, which can help expedite the resolution process by the Application development team.

What are Expensive SQL Statements?

Expensive SQL statements are long-running statements that cause performance problems such as:

  • High CPU Utilization

  • High I/O Loads

  • Database Locks

  • Negatively impacting the performance of other transactions

  • SAP work processes being blocked for a lengthy period

  • Expensive SQL statement monitoring helps you to define demanding processes happening on the system while experiencing performance bottlenecks. For these reasons, we always recommend customers set up monitoring of their expensive SQL statements.  

Keeping your systems and applications operational is crucial for companies but keeping track of trends over extended periods of time is critical for identifying bottlenecks and issues. Having to log into databases every day to retrieve performance counters without keeping track of previous ones complicates analyzing these patterns.

With IT-Conductor, monitoring your SAP SQL statements with related long-running background jobs is very quick and simple.

IT-Conductor collects data on current transactions, locks, checkpoints, user connections, timeouts, free memory, buffer cache hit ratio, deadlock alerts, and top expensive SQL, allowing you to monitor what is going on in the system in real time. You can also obtain historical interval time base collectors, so you can quickly pinpoint where the problem began and adjust without compromising the performance of your applications since the analytics is done in the IT-Conductor cloud platform, not on the managed system.

Additional benefits of running IT-Conductor as the Application Performance Management (APM) and Observability platform, is to put less load on your managed system because the expensive statement trace is not required to be activated on your system. IT-Conductor uses intelligent, policy, and threshold-based triggers to analyze the SQL when a pre-defined condition exists, which helps to relate the analysis to the monitored KPI.

Using thresholds and overrides to maximize the potential of IT-Conductor in areas where we have discovered any type of problem and get notifications in the event of an alarm.

All that is needed to get started tracking expensive and long SAP SQL Statements is to define the recovery activity in the background job monitoring threshold override.

 Thresholds for long-running job
Figure 1: Thresholds for Long-running Job
 

When an alert notification for a long-running job is generated, a recovery action activity is also triggered (the trigger process and frequency can be configured in a number of ways). 

 
In-flight Time
 Figure 2: In-flight Time
 

The in-flight SQL statement can be viewed by selecting from the log of recovery activity generated for associated long-running jobs, or it can be emailed to subscribers as part of the recovery action. More data from the SQL cache, including the statement ID, can be used for further application analysis such as explaining the plan and related execution metrics.

 
Recovery Activities Launches
 Figure 3: Recovery Activities Launches
 

Key Takeaway

To maximize the benefits of monitoring long-running jobs, it is necessary to ensure that there is a time-synchronized correlation of SQL statements with other performance-related factors that are also being monitored. This synchronization of related factors or events will enable a holistic view of what is happening in the system when experiencing system performance degradation. The view will enable support to see the effects of the long-running job in your system and quickly perform a root cause analysis, taking precautions to avoid similar issues in the future.

IT-Conductor assures an efficient way to reduce maintenance windows that may cause interruptions or system crashes due to cumbersome and manual processes that are not ideal for daily operations and business needs. With automated procedures, IT-Conductor combines time-consuming and labor-intensive chores into an automated process that eliminates the wasted time and need to exert effort.

 

Schedule a Demo