Select language:

SQL Monitor

In a productive ABAP-based SAP system, a huge number of various SQL requests are executed by the most diverse business processes. To detect performance hotspots and also identify potential for optimization in the SQL profile, you require specialized SQL Monitoring tools and utilities running in a live ABAP system.

The SQL Monitor allows you to monitor all SQL statements and operations that are executed by running ABAP applications. The collected SQL Monitor data provides you with aggregated performance indicators (number of executions, execution time, number of effected rows, and so on) for all executed database operations.

In contrast to the standard Performance Trace tool (transaction ST05), the SQL Monitor enables you to monitor the SQL activity system wide and over longer period of time. In addition, you can expand the scope of t he analysis to finding system-wide hotspots. The reference to the ABAP context is always retained, except for mere DB monitoring. For each SQL trace record, the entry point of the respective request (transaction code, submitted report, RFC function module, and so on) and the affected ABAP source code position is stored as well. This provides the first connection from the SQL statement to the affected business process.

You can use the collected SQL performance data to answer questions such as these:
  • Which SELECT in the customer code is executed most frequently in your system?
  • Which SELECT statements cause the longest runtime?
  • Which SELECT in the customer code reads the most data?
  • What does the SQL profile of a report X or transaction Y look like?
Overview of Features Provided
  • You can run the SQL Monitor in a productive system in parallel to productive use since the performance overhead for the traced business processes is negligible. (In average <3% overhead for the running processes).
    Note The data collection of the SQL Monitor is implemented directly in the ABAP kernel in a highly optimized manner to ensure that live operation of the SAP system is not disrupted.
  • You can switch on the SQL Monitor globally for all servers in the system or for individual servers only.
  • SQL Monitor traces each and every SQL statement executed in an ABAP program. This includes OPEN SQL, native SQL, and SQL statements coming from the ABAP kernel.
    Note Take into account that the runtime SQL data that are recorded in the SQL Monitor are NOT client-specific - that is independent from the DB tables's client ID (MANDT).
  • SQL Monitor collects performance indicators for each traced SQL statement, including:
    • Number of executions for the SQL statement
    • Execution time (maximum, minimum, average, standard deviation) for the SQL statement
    • Number of database records ( maximum, minimum, average, standard deviation) that have been retrieved or modified by the SQL statement
    • Number of internal sessions in which the SQL statement in question has been triggered
  • SQL Monitor provides you with a source code-oriented or, alternatively, a request-oriented view of the aggregated performance data.
  • With the time series functionality you have the option to concentrate on SQL monitoring results for specific time intervals. For example, you might want to verify the positive effect of performance-related corrections or of other events that could have an impact on the system performance. Or, you want to intentionally omit time intervals with untypical system events, as they could distort the performance results you want to analyze. Besides that, by using time series you automatically get a history of your system’s performance, which might be interesting on its own.
  • Based on the result list of performance data, you are able to derive the concrete entry point from each business process driving the traced SQL statement. A request entry point can be an ABAP report, a transaction, an RFC module, an URL, a batch job, or an update task.
  • Activating SQL tracing for individual database statements.
  • You can export your SQL Monitor recordings from the current system to a local file and then import it into the development system where the corrections have to be performed.
Note In particular, you can avail of the SQL Monitor features when you proceed to optimize the existing custom ABAP code in the course of database migration to SAP HANA.