FAQ: MaxDB Performance
1. Where do I find more information about MaxDB Tuning?
For information about MaxDB Tuning, see the SAP Developer Network (SDN) under the following link:
2. Can I use this FAQ to solve all MaxDB performance problems?
Analyzing database performance is a complex subject. This FAQ does not claim to provide the solution for all performance problems. Instead, it offers a general overview and references to more detailed notes and documentation that could help to solve the problem.
3. Which prerequisites are required for a detailed performance analysis?
To carry out a global performance analysis, the system needs to be balanced. To this end, the reads in the data cache (see Transaction DB50) should have reached at least 20,000 000. Otherwise, statements concerning the buffer quality must be limited.
The prerequisite for starting a performance analysis is that the database monitoring is active. This database monitoring is started by default when you start the database. You can check the status of the database monitoring by using Transaction DB50 Current Status -> System Settings. The MONITORING status information must be set to 'on'.
Make sure that you have started the Database Analyzer. You can start the Database Analyzer by using Transaction DB50 Problem Analysis -> Performance -> Database Analyzer -> Bottlenecks.
The Database Analyzer collects all data relevant to performance in an interval of 15 minutes by default and stores it in special files.
You can display the output of the Database Analyzer directly in the Bottleneck Analysis. For a detailed analysis, the Expert Analysis is also available.
4. How can I determine whether the general database performance can be optimized?
You can use Transaction ST03 or ST03N to determine how much of your system's overall response time is taken up with accessing the MaxDB database. This portion is determined by using the "Database time" column and should not exceed 40% of the overall response time.
If in addition you see Warning 3-type messages in the Database Analyzer log - especially poor data cache hit rates - then you should start a database analysis.
5. Can I also use the SAP system to display the Database Analyzer output?
Yes. You can use Transaction DB50 (-> Problem Analysis -> Performance -> Database Analyzer) to choose between the general and the expert analysis.
We recommend that for a first analysis, you use the Bottlenecks menu path.
6. Where can I find documentation about the Database Analyzer?
See Note 767598 and follow the link given there for the Database Analyzer handbook.
7. How can I tell how much time a particular transaction spends on the database and on which activity?
You can derive the database time of a transaction step from the respective statistical data record in Transaction STAD (formerly: STAT).
Note that for extremely long-running programs, there overflows may occur in the components of database time (for values > 2^32 microseconds = 71 minutes), and the subsequent time is wrongly recorded as processing time.
To determine which table accesses are mainly responsible for the database response time, you can set the stat/tabrec R/3 parameter (see Note 579462) to a value greater than 0 (-> number of tables to be logged).
If this parameter is used in connection with background jobs, the additional statistical data is not stored in the statistical record of the actual job, but in a statistical record of the RSBTCRTE report logged at the time.
8. How can I access performance data if I am in the MaxDB?
All performance-relevant data is stored in MaxDB system tables, known as monitoring tables. There is more information about the monitoring tables in the MaxDB documentation (Note 767598) under the keyword 'System Tables'.
Use the Database Analyzer to access the performance data. The Database Analyzer tries to write an informative message into the log files to allow you to identify the cause of the problem, and to remove it.
If required, you can carry out a detailed analysis by using the Expert Analysis of the Database Analyzer.
In addition, you can use the 'x_cons' development tool for interpreting general performance problems during a detailed analysis. x_cons output is also considered in the Database Analyzer and is issued in the log file in a form that is generally intelligible.
9. How can I access MaxDB performance data if I am in the SAP system?
You can access the Database Analyzer log by going to Transaction DB50 -> Problem Analysis -> Performance -> Database Analyzer.
You can access the database console (x_cons), by going to Transaction DB50 and selecting Current Status -> Kernel Threads -> Task Manager.
You can use the command monitor if you need to look in the special SQL statements to find the cause of the performance problems. To do this, see notes 819324 and 725489.
10. In which time unit is performance data usually specified in MaxDB and the SAP System?
In Transaction DB50, the time units for time specifications are generally specified in the menu.
In the command monitor, the measurement values are given in seconds, although you should note that you need to specify milliseconds for the monitoring settings.
In the resource monitor, the performance data is always specified in seconds.
In the Database Analyzer, all time specifications are in milliseconds.
In the database console (x_cons), all time specifications are in milliseconds.
In the SQL trace (Transaction ST05) the time specifications are in microseconds.
11. What general checks can be carried out if there are MaxDB performance problems?
a) MaxDB parameterization
Read the corresponding parameter note depending on the MaxDB version you use. If your parameter settings do not correspond to the recommendations, correct these parameters.
MaxDB Version 7.5 - > Note 767635
MaxDB Version 7.6 - > Note 814704
b) R/3 parameterization
Make sure that the rsdb/max_blocking_factor parameter is set in accordance with the recommendations. See Note 819324 and the notes referenced there.
As of the 6.10 Kernel, FOR ALL ENTRIES is converted into a list, if this is possible. In connection with this, it is necessary to correctly set the rsdb/max_in_blocking_factor parameter to optimize database performance. For more precise details, see Note 819324 and the notes mentioned in it.
Note that even in 6.x, FOR ALL ENTRIES are still sometimes converted into OR concatenations - more specifically, if several conditions in the WHERE section refer to the FOR ALL ENTRIES list. Therefore the rsdb/max_blocking_factor continues to be relevant.
To ensure that the database interface generates useful hints, the dbs/ada_use_hints parameter must be set correctly. In this regard, see Note 832544 and the subnotes that are referenced in it.
The _PACKET_SIZE parameter defines the size of the buffer that is used to send and receive SQL data. You should not normally change the default values. However, in the BW environment, operations such as Array INSERTs can be accelerated by increasing the parameter, since this means that less database communication is necessary. In this regard, see notes 545385 and 140739
MaxDB determines the statistics on the basis of estimated values that are defined in relation to the table size for each table in the domain.tables system table. See Note 808060 for information about setting the correct estimated values on your system. You should restart the report mentioned in Note 808060, especially after carrying out large data imports.
Make sure that you regularly refresh the database statistics by using Transaction DB13. You should schedule a UPD_CHECK followed by an UPD_COND once a week at a time when there is little load on the system.
You should only use Transaction DB13 to execute a 'CREATE NEW OPTIMIZER STATISTCS' (UPD_UNCOND) after large data imports, after a release upgrade, or after a request using MaxDB Support.
d) MaxDB Patches
Old MaxDB patches still sometimes contain errors that can lead to performance problems. Therefore check whether there is a newer MaxDB patch on the Service Marketplace than the one that you are currently using. To find out if there are performance improvements in a more recent patch, see the following notes:
MaxDB Version 7.3 - > Note 685873
MaxDB Version 7.4 - > Note 710165
MaxDB Version 7.5 - > Note 710166
MaxDB Version 7.6 - > Note 806690
e) Disk access times
You can use transaction ST06/OS07 (-> Detail Analysis Menu -> Disk) to determine the load on the hard disks of the database server. The load of a hard disk should not exceed 80%.
You can use Transaction DB50 (-> Current Status -> I/O Operations -> Overview) to determine the number of read and write accesses for each volume of data. Since backups are not written at the same time, there is a specific analysis by using I/O Operations -> Backup I/O, although this can only return data while the backup is running.
Even the duration of a savepoint can provide information about the hard disk speed. Check the runtime of the savepoint in the kernel log (knldiag). When you do this, also see the specification of how many data pages had to be written for the relevant savepoint.
Increased I/O times could also be the result of a high load. You can use Transaction DB50 (-> Current Status -> Activities Overview) to determine the number of physical read accesses carried out since the last database restart. Together with the Database Analyzer specifications, this gives you an impression of the system load.
You can use the SQL optimization (Note 819324) to determine the SQL statements that create a high read load. These statements can considerably ease the load on the system by creating suitable indexes.
In connection with this, see also Note 820824 FAQ:MAXDB that also provides recommendations for the configuration.
f) Hardware Bottlenecks
Use transaction ST06 or OS07 to check whether there are sufficient CPU and memory resources on the database server. The CPU IDLE time should appear on the hour average at 30% or more. Each hour, a maximum of just 20% maximum of the memory should be swapped.
g) Size of the data cache
Use Transaction DB50 (-> Current Status -> Memory Areas -> Caches) to check the hit rate of the data cache. This should be 99 % in a balanced system. If this is not the case, you need to check if the low hit rate is due to the size of the data cache being too small, or due to an unsuitable SQL statement. If necessary, you can increase the data cache by increasing the MaxDB parameter Data_Cache_Size (lower than 7.4) or Cache_Size (7.4 or higher).
h) Size of the catalog cache (CAT_CACHE_SUPPLY)
Use Transaction DB50 (-> Current Status -> Memory Areas -> Caches) to check the hit rate of the catalog cache. This should be 85% in a balanced system. It is not a cause for concern if the catalog hit rate is temporarily lower, since accesses to the system tables and an active command monitor can temporarily impair the hit rate. If the catalog cache is busy, the pages are paged out in the data cache rather than on the hard disk.
i) Log queue overflows
Log entries are not written directly to the log volume, but first into a buffer (LOG_IO_QUEUE) so as to be able to write several log entries together asynchronously with one I/O on the hard disk. Only once a LOG_IO_QUEUE page is full is it written to the hard disk.
However, there are situations in which you need to write LOG entries from the LOG_QUEUE onto the hard disk as quickly as possible, for example if transactions are completed (COMMIT,ROLLBACK). The transactions wait until the log writer reports the OK informing them that the log entry is on the hard disk.
Firstly, this means that is important to use the quickest possible hard disks for the log volume(s) and secondly, you must ensure that no LOG_QUEUE_OVERFLOWS occur in production operation. If the LOG_IO_QUEUE is full, then all transactions that want to write LOG entries must wait until free memory becomes available again in the LOG_IO_QUEUE.
At LOG_QUEUE_OVERFOLWS (Transaction DB50 -> Current Status -> Activities Overview -> LOG_IO_QUEUE Overflow) you need to increase the LOG_IO_QUEUE parameter.
j) Costly SQL statements
In accordance with Note 819324, check which SQL statements are responsible for the most disk accesses, and whether they can be optimized. You should also optimize SQL statements that have a poor runtime and poor selectivity.
k) Missing indexes
Using "DB02 -> Missing indexes", you can determine whether indexes that should exist from the R/3 Dictionary view are missing at database level. If indexes are listed here, determine the cause and create the indexes where necessary.
l) Network problems
Network problems between R/3 instances and the database server can lead to database response times seeming very long, even though from the MaxDB, you cannot see any problem. The NIPING tool is a good way of locating problems in the network area. See Note 500235 for details and start a NIPING server on the database server and NIPING clients on the R/3 instances. Check whether the measured bandwidth corresponds to your expectations. If not (or if it causes errors), consult your hardware partner.
m) Table buffering
Check the following points by using Transaction ST10 ("all tables"/"since startup"/"this server"):
-> sort the list in accordance with the column "Rows affected" that is a measure for the database load caused when accessing the table. If buffered tables (the "Buffer state" column is not empty) are at the very top of the list, then you should check their buffering. Large tables and tables with frequent changes should not be buffered. You must also check whether the WHERE conditions from the application match the type of table buffering.
-> Sort the list in accordance with the columns "Invalidations" or "Changes", and check whether it would be better to remove tables from the buffering if they have a high number of changes or invalidations.
-> Sort the list in accordance with the column "Buffer size" and check whether it is useful to buffer the tables with the largest buffer size.
There is extensive information about monitoring and about the optimization of table buffering in the book entitled "SAP R/3 Performance Optimization" from the SAP PRESS series.
12. Is there a note for parameter recommendations?
Yes, there are special notes for the MaxDB versions that give parameter recommendations concerning performance:
Version 7.6: Note 814704.
Version 7.5: Note 767635.
13. How do I proceed if I come across lengthy database accesses in SM50 or SM66?
The first step should be to determine the database task (and possibly the database session as well) that belongs to the work process. To do this, note the PID from SM50 or SM66 and search in Transaction DB50 -> Current Status -> Kernel Threads-> Task Manager
First, find your application PID under Active Tasks.
Under ID, you are given the database task and under the session ID column, the database session connected to the work process.
The Task State column shows you how the database task of your application is currently occupied, and can provide the first indicators for the cause of the performance problems.
In the MaxDB documentation (Note: 767598), you can branch directly to the description of the task states by going to the glossary and following the term task states.
For the detail analysis, you can then use the Database Analyzer and the command monitor. Note that the command monitor must first be activated before you can send an SQL statement to the database for it to be logged in the command monitor.
If you cannot find your application PID in this list, then your application is currently not active in the database. It could be waiting for database resources.
You should therefore look for your application PID under 'Executable tasks'. If it is there, your application is if waiting for a database resource.
The Task State column provides further information about the reason for the queue. In the MaxDB documentation (Note: 767598), you can search in the glossary for the term task states and branch directly to the description of the task states and find out the cause of the wait situation.
The output of the database console can also help you in this case to analyze the cause of the resource problem. For information about the database console, see the MaxDB documentation (Note 767598).
However, if you cannot find your application PID under executable tasks either, then the application is not active in the database at all. You can see whether it still has any connection to the database by looking under User Tasks.
Here too, you need to check the status of the corresponding database task.
If the task belonging to your application PID is in the COMMAND WAIT status, then the application has not sent an SQL statement to the database. This means that the performance problems are not to do with the database.
If you cannot find your application PID in this overview either, then the application is no longer connected to the database. The cause of the problem is not the database. The database connection could have been terminated due to network problems. For information about the causes of this, see the Developer Trace of the work process in the Appl-Diag or in the x_server log.
14. Where is there an overview of the MaxDB performance problems known to date?
There is no specific note that describes the performance problems that we are aware of to date. However, you can use the following feature notes to check which patches contain optimizer changes.
Version 7.6: Note 806690.
Version 7.5: Note 710166.
Version 7.4: Note 710165.
Version 7.3: Note 685873.
15. Where can I find BW-specific information about database performance?
Note 567745 gives an overview of the aspects relevant to performance in the BW-3.x environment. For BW 2.0B, see Note 184905.
16. What do I do if the database hangs completely?
The Database Analyzer and the database console can suggest causes. For more information, refer to the expert Note 936058 FAQ MaxDB Runtime Environment
1. If all user tasks are in the VWAIT status, they are waiting for an SQL lock to be released or for a checkpoint to be completed. In this case, you need to examine the SQL wait situations so as to identify the task that is holding the lock.
2. If the log area or data area is full, the running of the database is stopped until the log area is saved (if the LOG is full) or a new data volume is inserted (if the database is full).
Inform MaxDB Support immediately by sending a customer message to the BC-DB-SDB if you cannot analyze the situation and solve the problem on your own. For the analysis you require an operating system connection and an SAPDB connection (Note 202344). If there is no longer a lock situation, the causes for and reasons behind the downtime can no longer be analyzed.
17. Why does a database action often take far longer when it is first executed than in subsequent executions?
The longer runtime is mostly triggered by required data pages being imported by the hard disk (I/O). With the following accesses, the pages are already in the data cache, which may significantly reduce the runtime.
You can analyze this behavior by using the command monitor and the Database Analyzer. If the access to a table is not optimal, too many data pages are loaded into the cache and other data is displaced from the cache. This then leads to poor data cache hitrates and to many I/O accesses to the hard disk, which can be removed by appropriate tuning measures (increasing the data cache, creating suitable indexes, and so on) after a thorough performance analysis.
See also Note 819324 FAQ MaxDB: SQL optimization.
18. How can I examine poor performance at INSERT statements?
While SELECT, UPDATE, and DELETE statements are converted on the basis of execution plans, INSERT statements work with a completely different mechanism.
With INSERT statements, there is no option of improving the performance by using SQL tuning. Instead, you need to thoroughly analyze the performance problems that occur.
To be able to analyze performance problems with Inserts, you should know how a record is inserted into a data page:
If a record is inserted into the database on MaxDB, the system first finds the data page that is changed by the action. The required space is then made available if required by carrying out adjustment operations. The database administrator does NOT need to take ANY action for this!
When you insert a data record:
-> the data record is inserted into the target page at the end of the occupied data area,
-> the system uses an entry in the item list that is at the end of each data page and that only contains the reference to the record to sort to keep at a minimum the number of bytes moved.
The data pages of MaxDB are organized in such a way that the data area grows into the page from the start and the sort list from the end.
The data records are therefore not sorted at the insert, but only if an adjustment operation becomes necessary. When you move them from one data page to another, it is advantageous to have a sorted block, since you then do not need to copy record by record, but instead you can straight away move entire groups of records.
Let us first assume that the record fits into the data page. MaxDB simply places it at the end of the area available in the page and the item list is then updated. In doing this, the address of the new entry is sorted into the correct place on the item list. The item list is sorted accordingly.
Let us assume that during an INSERT, the new data record no longer fits into the corresponding page. A new page is then generated into which the new data record and half the data records of the page that was too small for the INSERT are written. The corresponding records of the original page are deleted.
Where necessary, the pointers to the next pages are refreshed. In addition, the address and separator information is entered via the new page into the B* Index page above it. However, if this does not fit into the B* index page either, a new page must be inserted here too. If the B* tree is no longer able to include the page, in other words if there is no longer any space available in the root page itself to insert a new branch, the system must (implicitly) add a new level to the entire B* tree.
These actions can be carried out very quickly, since they occur in the data cache, but at the same time they are also CPU intensive. You should therefore note the data cache hit rate and CPU utilization during the performance analyses.
If data pages are changed, this access must then be synchronized so that it is not possible for two end users to change the same data page at the same time. For this reason, for the time of the change, the system sets what are called B* tree locks, which should not be confused with SQL locks.
B* tree locks are generally only held very briefly for the time of the operation on the database.
For each access to the B* tree, the corresponding pages must be locked. In Version 7.5 and higher, these locks are no longer managed in separate lock lists, but are managed directly in the data cache. Locks are requested when you access the required data page in the data cache.
Advantages in terms of the locking concept in Version 7.3 and 7.4:
An important feature and thereby also the actual disadvantage of the old concepts was that the locks for the pages of a B* tree were managed in a separate component that was called the tree lock list. If this list was accessed many times in parallel, there could be collisions and therefore performance problems.
Even a large number of indexes can impair performance, since each index for the table is extended during the insert which means that even the index trees in the data cache need to be read and changed.
If you have already noticed (DB Analyzer or SQL Trace) that inserts take an excessively long time on your system, use the database console (x_cons) for the analysis.
As already described, you first determine the TASK-ID of your work process that is occupied with inserts. Then activate the time measurement for the console: x_cons <DBNAME> time enable and use x_cons <DBNAME> T_C <TASK-ID> to look at the task details.
Repeat this command every few seconds. If the task remains in wait status (VSUSPEND) for a long time, you need to determine the cause of the wait status. You may need to contact SAP Support by opening a customer message under the component BC-DB-SDB.
19. Is it worthwhile sorting loading processes (INSERTS)?
The decisive factor is the size of the table and the size of the data cache. If all data pages of the table to be loaded are in the data cache, then there is no point in sorting the data to load. From a performance point of view, the sorting may thereby be unnecessary, but each unsorted insert runs the risk of deadlocks. When you make an unsorted insert you must therefore ensure, by using the application logic, that there cannot be any deadlocks. In this way, small tables can be loaded without being sorted. However, if you have large tables and not all data pages could be loaded into the data cache, then you need to sort the data to load it. Loading unsorted data into large tables generates a high I/O-load and cannot be carried out efficiently.
20. Why should I optimize batch jobs as well, since their runtime actually does not matter, given that it takes place at night?
If batch jobs (background jobs) load an unnecessarily large amount of data into the data cache due to missing indexes in the post-processing, and this impairs the data cache hit rate, you can assume that the application data of the daily business has been displaced from the data cache to the hard disk. This data must be loaded back into the cache from the hard disk at start of the daily business when you execute the applications. The nightly background jobs are thereby indirectly responsible for poor response times when you start the dialog daily business.
This means that you also need to optimize jobs that run in the night.
21. What does "Sequential Read" and "Direct Read" mean in SM50/SM66?
"Direct read" refers to accesses in which a maximum of one line is returned by the database. This includes fully-qualified primary key accesses.
A "Sequential Read" refers to all other read database accesses, in which there may be more than one line returned. The term "Sequential read" is deceptive since it implies that blocks are always read sequentially (as in a full table scan). In reality, it is mostly a key or index access.
"Direct read" and "Sequential read" in SM50 are generally worthless for a performance analysis. It says no more and no less than that the process is busy with processing a query directed at the database interface. However, you cannot tell whether the queue time is triggered by physical I/O, database locks, the network, the MaxDB client, DBSL, DBI, or for other reasons. For a more precise analysis, you should always carry out an analysis of the database response time in accordance with Note 819324.
22. Where can I find statistics about the I/O response time?
For more information, refer to Note 748225 Measuring IO times on liveCache/SAP DB/MaxDB.
If you have created the data and log volumes with the "file" type and your I/O system has poor response times, also refer to Note 993848.
23. From where can I get more help for MaxDB performance problems?
SAP currently provides two training courses that include the topic of SQL optimization.
a) WB550: MaxDB Internals
This course gives you a better insight into MaxDB Internals and devotes one chapter to SQL optimization.
b) UMEW60: MaxDB/SAP DB Performance Monitoring & Optimization
This course allows you to execute an SQL optimization remotely on your own system together with the course provider.