SQL Performance Trace
In the last blog post we saw Analyzing Objects i.e. what to analyze? Where to analyze? And who should do the analysis?
In this post we will be focusing on how the data is gathered and where in this process we are facing the performance bottleneck.
If in the single statistics records you find that there is a problem during database server processing, perform an SQL Performance Trace.
There can be following areas where the database server processing may be hampered.
To obtain an overview of the behavior of the R/3 System, you can record various processes during the execution of an individual object. To analyze performance problems with database accesses, activate the SQL trace and the buffer trace from Transaction ST05 (see below).
The SQL trace records SQL statements. There may be considerable differences between the SQL statement formed on the ABAP level and the SQL statement that is received by the database.
A buffer trace lets you trace SQL statements that access buffered tables. SQL statements that access buffered tables can cause buffer reloads, which are indicated in a buffer trace. An SQL statement accessing a buffered table can result in a completely different SQL statement if it cannot be satisfied by the R/3 table buffer.
Additional tools include the enqueue trace, which records enqueue requests or releases, indicating the enqueue key and the object involved; as well as the RFC trace, which records the RFC calls received or sent. These tools are not explained in this post right now. We will discuss these tool later.
Prerequisites and precautions:
Before activating an SQL Performance Trace for a report or a transaction, you should generate the ABAP source code. In addition, you should execute the report once to avoid the buffer load process for the R/3 table buffer, the R/3 program buffer, and so on, being included in the subsequent trace.
When activating the trace, ensure that you are not using multiple user sessions or running other processes such as background jobs or update processes under the same user ID on this application server. These activities would make the SQL Performance Trace difficult to interpret.
The trace can be activated in the production system without any risk of creating errors or inconsistencies.
For each application server there is only one active trace file. Therefore, on a given application server, only one user can activate a trace.
The default size of the trace file is 800 KB. SAP recommends increasing the size of the trace file to 16 MB, by setting the parameter "rstr/max_diskspace" to 16,384,000.
SQL Trace Overview:
The flow diagram explains how the ABAP requests are served by the Database server.
The R/3 database interface receives an SQL statement from the ABAP program and converts it to an SQL statement that can be processed by the database.
For each database operation, the SQL trace will capture the time taken from the point a request is initiated to the receipt of the result on the application server via R/3 database interface (which also include the time taken by the network between application server and database server).
The data written to the trace file includes the database response time, a time stamp, the number of transferred data records, the database return code, the text of the SQL statement, and additional administrative information for each database operation.
In the next post we will see how to analyze the trace output and how to improve on SQL so that the turnaround time is under control.
Keep checking for new content.