Procedure documentationAnalyzing Database Performance Locate this document in the navigation structure

 

The performance of a database depends on the configuration of the database, the database design and the formulation of SQL statements. The following information is only a general overview; depending on your system environment, additional actions might be required.

Prerequisites

  • Before carrying out a performance analysis, the system this should have been running for some time: the read actions in the data cache should have reached at least 20,000,000.

    More information:

  • Regularly update the SQL optimizer statistics. Use performance analyses of individual SQL statements (particularly Join statements) only after the statistics have been updated.

  • Always have Database Analyzer running. When a SAP system or a SAP liveCache system starts up, Database Analyzer is automatically started for the system's basis database. If necessary, start Database Analyzer manually.

    More information:

  • In SAP systems, database monitoring is automatically activated when the database is started. This means that the statistical data which the system collects for monitoring goes back to the last database start. Depending on the performance issue you want to analyze, it may be necessary to reset the collected monitoring data.

    More information: Database Administration in CCMS, Database Monitoring

Procedure

For a performance analysis you can check a wide range of settings, access times and hit rates of your database system. It may also be necessary to include the applications running on the database into your analyses. As each performance analysis is highly specific to the system analyzed, the following steps are suggestions only.

To analyze performance issues, use Database Studio and CCMS (in SAP systems only). Experts can use further database tools, such as Database Analyzer and XCONS.

  1. Check your database parameter settings.

    For an explanation of database parameters, see Database Parameters and SAP Note 814704.

    If necessary, adjust the values of your database parameters. More information:

  2. Check disk access (read and write operations), especially for the data volumes. Long I/O times may be caused by a heavy system load. Check general database system activities such as running backups or disk read actions since the last database start.

    More information:

  3. 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 disk.

    More information:

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

    More information:

  5. To analyze performance bottlenecks in more detail, use Database Analyzer.

    More information:

    Bottleneck analysis provides a variety of statistical data covering a longer period of time. To get current data on I/O activities and execution times, you can also temporarily activate database time measurement in the database task manager.

    More information:

  6. If a performance problem has just been reported, you can specifically check the current user tasks. In the database task manager, activate automatic refresh and monitor the user tasks and their task states. Most user tasks should have the status Running. The status I/O Wait should be much rarer; statuses like Vwait, Vbegexlc and Vsuspend should not occur at all.

    More information:

  7. Display the open database transactions and examine these thoroughly.

    More information: Database Administration in CCMS, Transactions

  8. Check if exclusive wait situations have occurred. Exclusive write locks mean that other users cannot access the locked data. These locks can significantly reduce database performance.

    More information: Database Administration in CCMS, Wait Situations (Exclusive)

  9. To perform runtime analyses of any long–running SQL statements, use the special SQL command monitors (resource monitor, command monitor).

    More information:

  10. Indexes are essential for fast and efficient access to the data. Regularly check if indexes are missing from the database system or are damaged. Always recreate missing indexes immediately.

    More information:

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

    More information: SQL Optimizer documentation

More Information