Blocking is a process where a user is performing some operation on an object and another user also performs some operations on the same object at this time the blocking occurs.
The other user has to wait till the first user completes the operations or a lock occurs at the time.
Identifying the Blocking:
1. Identifying if the blocking is really present or not.
select * from sys.sysprocesses where blocked > 0;
2. After identifying the spid's. Which spid is the culprit.
DBCC INPUTBUFFER(SPID1)
DBCC INPUTBUFFER(SPID2)
3. Identify the login which spid belongs to which application / Login / Network
Select * from sys.sysprocessess where spid=<spid1>;
4. Priority should be choosedn accoring to the time started, CPU time, Memory usag, IO usage.
sp_who2
5. If confused on priority immediately escalate the information to the respective team / lead.
Kill the process if necessary if the process is going to the make the problems on the server on the production server also.
Comments