HOW TO ANALYZE PERFORMANCE ISSUES ARISING DUE TO LOCKWAITS
Here we will discuss about the easy procedure to analyze performance issues that may arise due to a lock waits deadlock situation. This situation usually arises when many jobs for a same process type (say Rollup) starts at same time and all these jobs wants to access same resource. In this case the waiting time for the jobs started at a later stage keeps increasing as they wait for the primary process to release the lock.
Checking Lockwaits and Deadlocks
Open DB performance monitor using TCode: ST04. Go to the Performance Tab and double click on Lock Waits and Deadlocks and this will display the number of lock waits existing in the system.
Here we will be able to see message as an Agent is waiting on another Agent. Expand the message and this will provide us with the Client Process ID number which is creating the lock wait, in this case 13697050.
Finding the Job creating the Lock Waits:
Now that we have identified the PID number we have to find the Job corresponding to this. For this open Global Work Process Overview using TCode: SM66 and then in the Find field provide the PID number collected.
The PID details with Server Name , Status , User Names and other details will be displayed.
From here take the Server name , in this case Server name is hqacid03_***_00
If we want to cancel the job creating the lock waits either we can open the Server from SM51 and can cancel the job using the PID or a better way would be to find the job and analyse the job log and then take a call on whether to cancel it or not.
Open SM37 and if by default the name of execution servers are not displayed for the jobs, click on Change Layout and add the field Executing Server.
Maintain filter on the Execution Server on which the PID which is creating the lockwait is being executed, hqacid03_***_00 in this case.
This will help to reduce the number of jobs that have to be checked. In this check the Job Details of long running jobs and compare the PID with the one that was collected from the ST04.
Once we receive a positive match check the Job Log and decide on whether to cancel this job or not. If the job is not progressing it is better to cancel the job so that the waiting processes can be progressed without further deteriorating the system performance.
Now after cancelling check for Lock waits and Deadlocks in ST04 and we can see that the number have changed to “0” in this case as it was created only by a single lock agent.
In case of more than one lock agents the same approach can be adopted for them individually.