This blog will explain about the SQLM tool to monitor expensive SQL statements in a productive environment.
Questions that will be answered are:
- How to start and configure the SQLM tool?
- How to analyze the results?
Configuration of SQLM tool
The SQLM tool does not require specific configuration or installation if you have a bit modern SAP system. To activate it start transaction SQLM and click on the Activate button for All Servers:
The trace is active now until the given time frame.
Example use of the SQLM transaction
First we start by writing a very bad performing Z program:
This program is really inefficient. After activation of the SQLM monitoring we run this program a few times.
Now we goto the SQLMD transaction (or from SQLM and then press display data button) to display the SQL monitoring results:
Selection can be done on total number of executions, execution time, amount of records. Result:
You see now the impact of our badly written program. Double click on the line will jump to the ABAP code point.
At the bottom of the SQLM start screen there are the buttons to create snapshots:
This results into the Snapshot screen:
You can create a snapshot here for later re-use. You can also download the snapshot to a different system by using these buttons: first export to file:
When you have the file, goto the target system and start SQLM and press create snapshot. Now use the option Create with Data Source File Import.
This is quite common practice to capture data in production by a basis administrator, who exports it, and hands it over to an ABAP developer using the data as upload in SQLM in the development system to improve poorly performing Z code.
More background information can be found in OSS note 1885926 – ABAP SQL monitor.
Useful blog (which is start of blog series on SQLM): link.