Analyzing expensive SQLs using SM50/SM66

This article explains the following query :

How to analyze expensive SQL statement using SM50/SM66 ?

Go to transaction SM66 and have a look at the global workprocess overview.

i) Have a look at Action column and identify any long running Sequential read/ direct read /insertion /updation etc

ii) If so, please note the respective report that is being run and the table that is being used for the same. From the report we can make whether it is SAP standard report or customized report. If it is customized, we can take help of ABAPer to see if the report can be finetuned in cases of bad programming. Table details can be used to figure out the size of the table and whether statistics are upto date for the table or not.

Please note :  "Updatestats job should run daily so that system will have recent statistics about all the tables. These statistics will be useful for Cost based optimizer to identify optimized execution plan for an SQL statement"

iii)  Also, please note the user who is running that process. So that, later user can be approached and a trace(ST05) can be kept for his activity to understand his transaction in detail which would help for finetuning.

iv) SM50 transaction can be used to view the detailed display of the process and the SQL statement that is being executed

For example, Please refer below screenshot of SM66 :

In the below screenshot,

i) Highlighted user is running ZFI_TDS* report on RBKP table in dialog mode and sequential read action is happening on the same.

ii) user DDIC is running RBDMONI_* report (in  background mode) on table BDCP2 and direct read is happening on the same. It has already consumed 783 seconds of CPU time.



To analyze point i) scenario in the above case, please note the server name on which this process is running. Go to that server through SM51 and have a look at SM50 transaction.

Identify the respective PID of the process which you have suspected as expensive SQL from the overview and double click on that process which opens up detailed display as below:

How to troubleshoot a background job running for long duration in sap?

How to troubleshoot a background job running for long duration in sap?

This article answers how to troubleshoot a background job running for a long duration

-------------------------------------------------------------------------------------------

1) First of all identify the job that is long running and identify details like job class, work process that is executing the job

How to find out long running jobs in SAP 



Goto SM37 transaction and select the active job status between any 2 given date/time and list them. In the output, sort the jobs based on duration column in descending order and identify the jobs that are running for longer duration



All other questions can be answered from the below :

Goto transaction SM37 and list the jobs based on status and time interval.

Select any job for which you want to figure out the details. Double click on the job,  which pops up "display job screen". In that screen, click on job details tab to view

• Job name


• Job class (i.e A, B and C)


• Status of the job


• Exec. Target (server/instance on which job is being run currently)


• Job frequency (hourly, monthly , weekly etc)


• Work process that is executing the job


• Client on which job is running


• Release time of the job


• Schedule start of the job

2) Click on the job to view the display job screen. In the screen, click on job log to understand what is being performed by the job currently. This may give details like job is currently extracting some data packages or processing data packages etc

3) Identify the executing server and process id of the job from the step 1 and goto transaction SM50 of the respective executing server to view more details about the background job running.

Figure out the status of the job like On Hold or running from the process overview. If the job is On Hold, find out the reason for On Hold by examing the "Reason" column of SM50 transaction. Reason for On Hold could be due to CPIC/RFC/DEBUG/ENQ/PRIV/UPD etc.

Double click on the reason column for detailed information on the same and troubleshoot accordingly. If reason is RFC, check out which RFC it is referring to and cross check whether destination system is up or not and any other problems with that system.

• If it is ENQ, check out any lock issues like lock overflow etc


• If it is PRIV, check out for memory bottlenecks


• If it is UPD, check out whether any update issues


• If it is CPIC, check out for any network , gateway, message server and other communication problems

4) After performing step3, if you figure out job is not on Hold and it is in running state, then examine report column to identify what report/program is being executed by the job. Once you got the report/program details, figure whether it sap program or custom program and take actions accordingly.

5) Also examine Action and table columns in SM50 transaction of respective executing server to identify what is the action( roll in/roll out /Sequential read/Physical read/insert/update/delete etc)  being carried out by the job currently and what is the table on which action is being carried out.

If it is sequential read, figure out the cost of that sequential etc and consider for indexing etc. If it is physical read, check out whether there are too many swaps and consider resizing buffers accordingly. If you observed delay is due to high roll in/roll out, identify reasons for the same and tune buffer/memory parameters accordingly.

6) Once you get the table details on which action is being carried out, figure out

• How many records are existing in the table?


• Is this taking long time due to volume of records?


• Are there proper indexes on the table ?(If no proper index, consider index creation by taking help of DBA )


• Is the table having up to date statistics? (If statistics are out of date, consider updating statistics of that table)

7) Consider debugging the process in SM50 (Program/Session -> Program ->   Debugging) to figure out the issue

8) Using ST05 or ST12, a trace can be taken for background job to figure out where exactly time is being consumed and to identify various CPU/memory bottlenecks or any buffer issues.

9) STAT/STAD transaction can be used to figure out what is the reason for high response time and actions can be taken accordingly

10) By taking help of ABAP er, even ABAP run time analysis can be done using SE30 transaction

By following the above steps, you can pin point the issue and take actions accordingly to minimize runtime of long running background jobs.

How to activate a Trace for a user in SAP system ?

How to activate a Trace for a user in SAP system ?

This article answers the following queries :

• What are the different types of traces in SAP ?


• How to activate a Trace for a user in SAP system ?


• How to do performance analysis in SAP system ?

What are the different types of traces in SAP ?

• SQL Trace (Trace for SQL statements)

• Enqueue Trace (Trace for enqueue/lock requests, waiting etc)

• RFC Trace

• Table Buffer Trace

How to activate a Trace for a user in SAP system ?

Goto transaction ST05  and you will get the below screen :



Select the type of trace you would like to activate and press activate trace to start tracing.

Please find below functionality of various functions in the above screenshot :

Activate Trace : This is used to activate the trace for the same user who is starting the trace



Activate Trace with Filter :  This functionality is used to trace the activity of a different user(other than the user who is starting the trace) and filter can be done to trace a  specific transaction or program.



Deactivate Trace :  This functionality is used to deactivate the trace




Display Trace :   This functionality is used to display trace



Click on tick mark to display the trace



Click on Yes to display the trace as below:



Enter SQL Statement : This functionality is used to analyse an SQL statement

Click on this pushbutton to view the below screen :



Trace Status : This area is used to inform about the status of the trace at that point of time like traces activated, deactivated etc

How to find transport Request….

I found that most of the people create transport requests for different objects and they forget about the request numbers. In case if they hadn't maintain the proper description of their request then it can create a headache for them. I hope this will be help full for my ABAPer fellows. Functional Consultant Can use the third method.

There are 3 main methods through which we can check transport request. I will show all of them with screen shots.

1.  In most of our ABAP transactions as SE11, SE80, SMARTFORMS, SFP, CMOD, SE51, etc. we can find our transport request for developed object through this method.

Go to SE11 give table name.



Go to the GOTO and select Object Directory Entry.



Click Lock Overview.



Double Click the Task/Request number given, highlighted in image.



And you are done. This is your transport request. Now select it, go to SE10 Release it and in STMS transport it to other layer in landscape.



The same method 1 you can use for SE80 as shown in fig.



We can use for SMARTFORMS as well.



2.   The second method is mostly used for code areas, but we can use it on other places as well where our method 1 doesn't give the result.

Go to SE80 open your program and go to Utilities. Select Versions and then go to version management.



Double click this number.



This is your Transport request.



3. This is the third method it is the most powerful method through which you can find all transport requests. Basically this is the database table where you have to give object type and its name and you will find its request number. This method can be used by functional consultants  as well.  As in our example we will find the Transport Request for Workflows.

Go to SE16. Open table E071.



Select Object type and press F4. It will show you the list of all objects. Search for your desire one. In our case it is PDTS.



Give Object Name in our case customized workflows range start with 999 so I give 999* and execute it.



This is your transport request list with your object names and there types.

Types of Work Processes

Even though the Work processes are unique at OS level SAP differentiated between the work processes based on the nature of work.
The processes are determined by the instance name DVEBMGS<Instance_No>
For Dialogue instances the work process will be denoted as D01, D02….

1. D – Dialogue


2. V – Update


3. E – Enqueue


4. B – Back ground


5. M – Message


6. G – Gateway


7. S – Spool

1. DIALOGUE ‘D’
It is only the process which communicates interactively with the users. There should be atleast 2 Dialogue work process per instance. Dialogue work process initiates Update, Background and Spool.

2. UPDATE ‘V’
It is used to update the transactions in the database. It is initiated by Dialogue process. There should be atleast 1 Update in the entire system. It is also recommended to have an update process for every 5 Dialogue.

3. ENQUEUE ‘E’
It is used to provide locks for the records that are going to be updated. It ensures consistency for updates. There will be only 1 Enqueue configured in the system during the installation. It is possible to have more than one enqueue provided they are installed or configured on the central instance.

4. BACKGROUND ‘B’
The tasks which are expensive or time consuming are scheduled to run in the background mode non-interactively. There should be atleast 2 background work process in the system

5. MESSAGE ‘M’
There should be only 1 message server in the entire R/3 system. It is used to manage all the dispatchers. It is used to load balance the requests to identify the least loaded dispatcher.
It is also used to provide locks to the request that are coming from Dialogue instances.

6. GATEWAY ‘G’
It is used to provide a means of communication with SAP and NON-SAP systems. There will be only 1 gateway for each instance.

7. SPOOL ‘S’
It is used to print the documents to a printer or output to a fax machine etc. There should be atleast 1 Spool process in the entire system. It is also possible to configure more spool process depending on the print/ spool volume.

Monitor SAP Systems

Monitoring SAP system is a must to make sure that everything is running as perfectly as possible. Any of these problems, which might suggest a small problem, might very soon creep up to be a big problem. So it is always recommended to monitor SAP systems everyday and it’s even better if you share these reports to the entire group of SAP technical members working in your project to make sure that they can identify something which was not identified by you or other team members.

Here is the list of things which should be monitored in SAP Systems frequently.

1. Check SAP Process Overview (SM51/SM50/SM66): Use these transactions to monitor work processes which are running for a long time. If you see any processes running in a Dialog mode for a longer time, then inform and find out from the users what they are trying to do.


2. Logged in users in AL08/SM04: This is used to check the no of users logged in the system, Make sure the users are evenly distributed across all instances, if you see a lot more users in one instance than the other, there is something wrong, check the load balancer settings or other options.


3. Spool Requests: Check the no of Spool requests created each day, there shouldn’t suddenly be a spike in the process unless there is a new process in place.


4. Lock Objects SM12: check this transaction for any locked objects. If there are any locks for more than a day, than that is a problem, find the user or the programmer to dig more further to see if this is really required.


5. Update requests on SM13: There should not be more than 10 updates at a given time.


6. System Logs SM21: Check the logs frequently for any abnormal trends, find corresponding system dumps (ST22) and contact the necessary developers or check OSS Notes.


7. Check Background Jobs: SM37 is used to check the background jobs. Check for any jobs running for a long time, check for past run time of the same jobs to see if there was a sudden increase in the duration.


8. SAP Buffers: User ST02 to check for the SAP Buffers, The “current use” should not be near the value of “in memory”.


9. Workload Analysis: Use ST03 is used to check the workload analysis of SAP Instance.


10. Operating System Monitor:  Use ST06, to check the Operating system status. Check the utilization of system resources, check the event logs. If CPU utilization is higher than 60%, find the programs and transactions and take corrective measures.


11. ST11 to display Developer traces.


12. St22 for ABAP dump analysis


13. St04 /ST02 for Database Analysis to monitor expensive Statements, monitor database performances.


14. WE02 for failed IDocs