Procedure documentationExecuting Performance Analysis in CCMS Locate this document in the navigation structure

 

Database administrators are often asked by users to find out why the performance of their SAP system is poor. There are many factors to take into account when tracking down these types of problems.

Note Note

There are some specific notes for SAP liveCache technology in Performance Analysis in the CCMS: SAP liveCache Technology.

End of the note.

Procedure

Your research can take a long time and you should therefore make sure you have as much background information as possible before starting.

  • Has the application/transaction always been slow, or has it only just started to slow down?

  • Is the problem in a new program or application/transaction?

  • Is the slowdown only during peak periods or is it fairly constant?

  • Have there been recent changes in workload?

  • Is only one application/transaction slow or are other transactions or applications also now performing poorly?

Attempt to localize the performance bottleneck using the information you obtained. If you feel this issue may be isolated to one particular transaction or application, you may also need assistance from the application developers. These developers understand the process flow of the application, and can help you to change and test SQL statements in the program.

Workload Overview

Use the Workload Monitor to get an initial overview of the response times of all components in your SAP system (Workload Monitor). In particular, note the average response time/dialog step (ms) for the dialog task in the workload overview. The average time for the dialog task should be under one second. If this value is critical, you can check the Of response time, database time (%) statistic to find out the proportion of the total response time of the dialog task which was used for database requests. If database requests take up more than 50% of the total response time, this may indicate that a database problem is causing performance bottlenecks.

Database Performance

Carrying out a database performance analysis means that you can check a wide range of settings, access times and hit rates of your database system. If necessary, the applications can then be included in further analyses. As each performance analysis runs individually, the following suggestions are merely a collection of ideas.

A series of CCMS transactions are available for you to help analyze the performance bottlenecks, in particular the DBA Cockpit, the Database or liveCache Assistants and the Database or liveCache Alert Monitor. Experts can also use additional database tools, such as the Database Studio, Database Analyzer, and XCONS. To set up and use your database optimally, you can automate many necessary DBA activities: Automating Database Administration Activities.

Note Note

The documentation for the SAP MaxDB database system also generally applies to SAP liveCache technology. If there are functional differences, these are explained.

End of the note.
  • Database monitoring is automatically activated when the database is started so that the statistical data from the last database system start is determined. You can reset the monitoring data, if necessary.

    Refer also to: Database Monitoring

    In order to carry out a meaningful performance analysis, this should be a running system. The read actions in the data cache should have reached at least 20,000,000.

    Refer also to: Caches

    The Database Analyzer must always be activated. When a SAP system or the liveCache starts up, the Database Analyzer is automatically started for the system's basis database. If necessary, you should start the Database Analyzer explicitly.

    Refer also to: Database Analyzer

    Update the SQL Optimizer statistics regularly. Use performance analyses of individual SQL statements (particularly Join statements) only after the statistics, if any, have been been updated. Schedule regular updates of the SQL Optimizer statistics in the DBA Planning Calendar. Ensure that the scheduling has been successfully executed and thus that the SQL Optimizer statistics are current.

    Refer also to: Updating the SQL Optimizer Statistics

  • Check your database parameter settings. For more information, refer to the explanations of database parameters and SAP Note 814704. If necessary, make corrections to the database parameter settings.

    Refer also to: Parameters

  • Check the disk access (for example, the read and write operations), especially on the data volumes. High I/O times can point to a heavy system load. Therefore, check the database system activities constantly (running backup, many read actions on the disk since the last database start, among other things).

    Refer also to: I/O Operations

  • Check the hit rates in the data cache and the catalog cache. Database performance can suffer if too much data has to be read from the disks. It is therefore important that most accesses to database tables and other data from the database be covered by data in the caches.

    Refer also to: Caches

  • Check logging activities. Specifically, there should be no log I/O queue overflows.

    Refer also to: Activities Overview

  • You can examine the cache hit rates and other database information in more detail by analyzing the bottlenecks with the Database Analyzer. Bottleneck analysis provides a variety of measurement data covering a lengthy period of time.

    Refer also to: Bottlenecks

    If necessary, you can also temporarily activate DB time measurement in the Task Manager and thereby get current measurement data on I/O and execution times.

    Refer also to: Task Manager

  • If a performance problem is currently being reported, you can specifically check the user tasks. Activate Automatic Refresh for the Task Manager and observe the user tasks and their task states.. Most user tasks should generally have the status Running. The status I/O Wait should be much rarer; statuses like Vwait, Vbegexlc and Vsuspend should not occur at all.

    Refer also to: Task Manager

  • Display the open database transactions and examine these thoroughly.

    Refer also to: Transactions

  • Determine if exclusive wait situations have occurred. Exclusive locks mean that other users cannot access the locked entry. These locks can significantly reduce the database performance.

    Refer also to: Wait Situations, SQL Locks

  • Use the Command Monitor to perform runtime analyses of any long-running SQL statements.

    Refer also to: Command Monitor

  • Indexes are essential for fast and efficient access to the data of the database. You should therefore make regular checks to determine whether indexes are missing from the database system or are defective. Always recreate a missing index immediately, since it is very important for database system functionality.

    Refer also to: Index Analysis

  • Check the SQL statements that cause the most disk accesses and optimize these statements, if necessary, based on these SQL statements.

    Refer also to: SQL Optimizer in CCMS, Database Administration, SQL Optimizer

More Information