3 Simple Steps to Identify Performance Hotspots in ABAP Custom Code

[venkatanarasimh]

Introduction

Optimizing custom ABAP code is not an easy process unless you have the right tools that will guide you in the optimization phase. Performance Analysts/ ABAP Consultants/ ABAP Developers who have performed performance optimization understand the challenges involved in optimizing the custom code.

Optimization complexity increases exponentially in a big ERP system landscape. A big landscape involves hundreds of business processes that execute several thousands of SQL statements each day.

Getting started is the trickiest part. Some of the questions worth considering before you plan the optimization task are:

• Which of the business processes in my productive system consume the most database time?
• What is the SQL profile of my main business processes?
• What are the most expensive SQL statements in my system and by which business processes are they triggered?

SQL Monitor is the only tool that answers all these questions. SQL Monitor View interprets SQL Monitor data and provides transaction statistics on the fly. With SQL Monitor View, performance optimization is no more a time consuming task. It automates the process of analyzing data, increases your productivity, and improves the efficiency of your business process. To know about SQL Monitor, see the SQL Monitor blog listed in the “Related Information” section.

SQL Monitor view is available in ABAP in Eclipse development environment with ABAP Development Tools in SAP NetWeaver 7.5 SP0.

Business Case

Customers and partners experience that some of their business processes continue to have performance issues even after migrating to SAP HANA.

Solution

A detailed research and analysis of multiple business processes has indicated that almost all the performance issues relate to improper SQL statements used in the ABAP custom code. For better performance, it is mandatory to adhere strictly to the Open SQL golden rules. For example, one such rule is to avoid nested select statements.

SQL Monitor view provides a simple solution. It identifies top business processes with performance issues and allows you to navigate to the SQL statements that cause such issues. It finds potential performance hot spots that need optimization in a matter of seconds and displays SQL Monitor Markers (visual indicators) to show case the severity of SQL statements.

SQL Monitor view uses SQL Monitor data to search and display results. The search feature uses the Aggregation By criteria to aggregate data based on any of the following options:

• Request: This option aggregates SQL Monitor data based on the request entry point. Request entry points are business processes such as transactions or reports that an end user initiates, or is initiated from an external source.
• Source Code Position: This option aggregates SQL Monitor data that contain the same SQL statement.
• None: This option does not aggregate data. The SQL Monitor data appears in the search result.

To sort data, the search feature uses specific database parameters in Order By such as:

• Total number of database executions
• Total database execution time
• Total database records

Getting Started

To analyze business processes with performance issues, enable SQL Monitor trace in your production system for a couple of weeks. If you are running a quarterly report or a transaction at a specific interval then enable SQL Monitor trace during this interval. Import the trace logs from the production system in the form of a snapshot to a development system with SQL Monitor view implementation.

Note

You can also perform analysis on your production system. However, to avoid loading of the production system we recommend that you use a development system after obtaining the trace logs.

Step 1: Configuring Data Source

         

• In the Window menu option, choose Show View > Other…
• In the search field, enter SQL Monitor.
• Choose OK.

              The SQL Monitor view appears.

        TIP: You can also use Quick Access (CTRL + 3) in the ADT toolbar to display the SQL Monitor view.

                In the Quick Access field, type SQLM. In the Views section, choose SQL Monitor.

• In Data Source Configuration, choose an ABAP project.

          The list displays all the projects available in the Project Explorer view.

• To configure a data source, click the Data Source hyperlink.
• Data Source Summary option ( )  would provide quick statistical information ( Standard Code vs Custom Code) like mentioned below
  • Total DB Time Distribution
  • SQL Statement distribution
  • Request distribution (Different request types)
  • Total DB Executions Time
  • Total DB Executions
  • Total DB Records
             

Step 2: Analyzing Business Processes

   

• To consolidate data, choose Aggregation By.

          The default option is Request.

    Note: If you want to identify and optimize business processes that take most

    of the execution time then choose the Request option. If you want to identify

    and optimize SQL statements that take most of the execution time

    then choose the Source Code Position option.

• To sort data, choose Order By.

          The default option is Total Number of DB Execution.

• Enter the number of records for analysis.

          The default number is 10.

• Choose Search.

          The SQL Monitor view lists the top records based on the search criteria that you have defined.

          Note: If you have not configured a data source for the ABAP project that you selected

          then a message to configure a data source appears.

• To analyze records, choose a record in the result set to display relevant details in the table on the right.

          Based on the search criteria that you have set, two patterns of analysis is possible.

          These patterns are as follows:

• Analysis based on business processes: If Aggregation By is Request

              then the SQL Monitor view displays the top request types sorted according to

              the most number of database executions or according to execution time or

              according to the most number of database records retrieved. Select a request type to see

              the SQL profile of the request type in the right side table.

             

• Analysis based on SQL statements: If Aggregation By is Source Code Position

              then the SQL Monitor view displays the top SQL statements sorted according to

              the most number of database executions or according to execution time or

              according to the most number of database records retrieved. Select an SQL statement to see

              the business processes that use the SQL statement in the right side table.

             

• To open relevant editors, choose an entry from specific columns available in the tables.

• To see request entry point related details in the editor, choose an entry

              in the Request Entry Point column.

              Press CTRL key and click the entry to open the corresponding editor.

• To see SQL statements that exceed the set threshold values, choose an entry

              from the Object Name column.

              Press CTRL key and click the entry to open the corresponding editor.

    TIP:    You can also press F3 after choosing an entry to open the corresponding editor.

    Note:  The SQL Monitor markers appear next to the SQL statements that exceed the threshold.

              The SQL Monitor Marker tool provides visual indicators ()  to indicate the

              severity level of the SQL statement.

             

• To view performance details of an identified SQL statement, move the mouse cursor

          over the visual indicator that appears in the editor.

         

Note :

The result set table also contains options

to “Filter Columns” () and search any text ( ) in the result set.

As Performance Analysts or ABAP Consultants or ABAP Developers you can use these options

to filter the result set based on your business requirements.

Step 3: Setting Performance Threshold

You set performance threshold to detect if the performance of a business process falls

outside a specified range and might cause problems. If a business process deviates from

the set threshold, SQL Monitor view displays visual indicators next to SQL statements in the business process.

• In ADT, choose the Window menu option.
• Choose Preferences.
• In the Preferences window, navigate to ABAP Development > SQL Monitor.
• Choose SQL Monitor.
• Choose the required SQL display configuration.

    Note

• If you need to analyze all SQL statements, choose Display all SQL statements.
• If you need to analyze SQL statements based on specific threshold values, choose Display SQL statements based on threshold.

              This option displays the default threshold values. You can also enter customized threshold values.

• Choose Apply.
• Choose OK.

    I presume that my blog will assist Performance Analysts or ABAP Consultants or ABAP Developers to unleash the potential of SQL Monitor view and efficiently identify business processes/SQL statements that require optimization.

Related Information

http://scn.sap.com/community/abap/hana/blog/2013/11/16/sql-monitor-unleashed