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.
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.
Database Studio, Displaying Information About Caches (Working Memory Areas)
Database Administration in CCMS, Caches
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.
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.
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. Because 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.
Check your database parameter settings.
If necessary, adjust the values of your database parameters. More information:
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.
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.
Check the logging activities. Specifically, there should be no log I/O queue overflows.
Database Studio, Displaying the Database Properties
Database Administration in CCMS, Activities Overview
To analyze performance bottlenecks in more detail, use Database Analyzer.
Database Studio, Activating or Deactivating Database Analyzer
Database Administration in CCMS, Bottlenecks
Database Analyzer documentation
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.
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.
Display the open database transactions and examine these thoroughly.
More information: Database Administration in CCMS, Transactions
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.
To perform runtime analyses of any long-running SQL statements, use the special SQL command monitors (resource monitor, command monitor).
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 re-create missing indexes immediately.
Check the SQL statements that cause the most disk accesses and optimize these statements, if necessary.
More information: SQL Optimizer documentation