Executing Performance Analysis in CCMS 
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.
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 theworkload 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 use further database tools, such as the Database Manager, the Database Analyzer and XCONS.
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.
See also: 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.
More information: 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.
More information: 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.
More information: 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.
More information: 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).
More information: 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.
More information: Caches
Check logging activities. Specifically, there should be no log I/O queue overflows.
More information: 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.
More information: 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.
More information: 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.
More information: Task Manager
Display the open database transactions and examine these thoroughly.
More information: 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.
More information: Wait Situations (Exclusive), SQL Locks
Use the Command Monitor to perform runtime analyses of any long-running SQL statements.
More information: 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.
More information: Index Analysis
Check the SQL statements that cause the most disk accesses and optimize these statements, if necessary, based on these SQL statements.
More information: SQL Optimizer in CCMS, Database Administration, SQL Optimizer
More Information
SAP Notes: 819641 (FAQ: MaxDB Performance), 819324 (FAQ: MaxDB SQL Optimization)
Database Administration, Analyzing Database Performance, Troubleshooting, SQL Optimizer Statistics, Database Parameters
Concepts of the Database System, Tasks
Documentation for Database Analyzer, XCONS
Note
The documentation for the SAP MaxDB database system also generally applies to SAP liveCache technology. If there are functional differences, these are explained.