4/25/2016 2:29:32 PM

  ABAP SQL Monitor - What is it and why do I need it?

During the SAP HANA migration of a productive Business Suite system the performance of main business processes shall be optimized. Focus of this document is the optimization of custom ABAP code.

A first step is of course to run and analyze the main processes (e.g. "Create sales order" using transaction VA01) by using standard performance analysis tools like SQL trace or ABAP runtime analysis.

However, this will only cover one path through the transaction and the retrieved performance data may not even be realistic since the data volume, scenario and usage of this transaction might be different in the day-by-day business in production.

Additionally you may miss many processes (e.g. RFCs, batch processes...) which represent main productive business scenarios containing a lot of SQL performance potential.

In conclusion, we need a tool, which monitors the performance of ALL ABAP SQL executions in production, and links back to the driving business processes.

This would allow answering questions like:

  • Which of my business processes have the highest total SQL execution time - and are responsible for the main load on the database? 
  • What is the productive SQL profile of my main business process e.g. report ZXY - and which SELECT is dominating this process? 
  • Which SQL statement has the highest execution time - and which business processes are affected by this slow SQL statement? 
  • Which of my business processes run a huge number of SQL statements or work with a lot of data? 
  • Which are the most often executed SQL statements in my custom code? 

This SQL Monitoring task cannot be done by the standard analysis tools since:

  • SQL trace, ABAP Runtime analysis, etc. contain all this information but they are designed to trace single processes and not a complete system. The trace files would get too big / overwritten and the performance overhead would be not acceptable when using these tools even for a short monitoring period. 
  • Statistics (STAD), Workload analysis (ST03) etc. monitor a complete system and deliver aggregated performance data for processes (transaction, report, RFC...) but they do not deliver detailed performance information about the different SQL statements. 
  • Database monitoring tools like SQL statement cache deliver very detailed information about the executed SQL statement, but the data is overwritten after some time, identical SQL statements coming from different ABAP programs are condensed to one SQL trace line and there is no link to the driving business process (e.g. transaction, ABAP report...) 

The new ABAP SQL Monitor fills this gap and provides total transparency of the running SQL statements in a productive environment.

The ABAP SQL Monitor:

  • Traces each and every SQL statement coming from an ABAP program - this includes OPEN SQL, native SQL and SQL statements coming from the ABAP kernel 
  • Can be switched on for all or dedicated servers of an ABAP system 
  • Can run in a productive system in parallel to the productive usage since the performance overhead for the traced business processes is negligible.(The data collection is highly optimized by using kernel functionality which buffers the performance data in different memory layers before storing it asynchronously on database) 
  • Collects performance data for each traced SQL statement including: 
    • number of executions
    • execution time (maximum, minimum, average, standard deviation)
    • fetched/changed rows (maximum, minimum, average, standard deviation)
  • Derives the entry point of each process driving the traced SQL statement. The entry point can be a transaction, report, RFC module or URL 
  • Writes new records for an executed SQL statement if one of the following key fields is different (if not then the performance data of the executed SQL is added to an existing entry (aggregation)):  
    • DB table name (s) - you may have several for e.g. joins or subqueries
    • SQL Statement location in ABAP (program, include, line)
    • Process type and process name ( e.g. transaction VA01)
  • Allows displaying the performance data aggregated by business process (request) or ABAP code location 
  • Allows downloading SQL Monitor data in order to import it in a development system where the code corrections are done. 

The following screenshot shows an example for the SQL data display of SQL Monitor without aggregation and ranked by executions


When is it available and what are the technical requirements?

SQL Monitor comes in two (more or less identical) flavors. As a standard delivery of NW (SAP_BASIS software component) and via the ST-PI add on. The ST-PI add on variant has been added to serve older NW releases (NW releases >=7.00) where the standard SQL Monitor is not available.

The standard NW SQL Monitor is recommended for the new NW releases like 702,731,740.

SQL Monitor variant Supported NW Release Kernel version
NW NW 702, NW 703/731, NW 740 For NW releases < 740: No support for 720 Kernel,use 721 kernel instead
ST-PI add on NW 700, NW 701, NW 702, NW 703/731, NW 710, NW 711, NW 720, NW 730, NW 740 For NW releases < 740:No support for 720 Kernel,use 721 kernel instead
Please refer to OSS note 1885926 for all details about availability and necessary preparation steps. The following SQL Monitor description focuses on the feature set shipped with NW 740 SP3 or via the ST-PI add on (2008_1_700 SP8)

In which system shall I use the SQL Monitor?
The SQL Monitor can run in any ABAP system but since it's task is to collect realistic SQL performance data it is normally used in a productive system or in a test system where "realistic" tests are running.

SQL Performance Monitoring-SAP SQLM Part-2
SQL Performance Monitoring-SAP SQLM Part-3

If you like this blog, please share (Facebook/LinkedIn/Google+) to click below links so it will reach to others.