SybaseBlog

I had a colleague come across a situation where an investigation of a performance issue revealed blocking in the database, but wasn't sure how to investigate further.

The first step is usually to execute sp_who.

1> sp_who
2> go

This will list all the tasks and what they are doing. Some of them may be waiting on locks being use by another task. This task will also be in the list, and will helpfully include the user and the machine where this task originated

We can get more information about these tasks from the sysprocesses table, using those spids from sp_who. Here we get to see the name of blocking process, and the process ID on wherever it is being executed.

1> select * from sysprocesses where spid = [spid]
2> go

Before you get up and go to that machine and ask the user what he is doing with that application, you can execute sp_lock to get more information about the resource the task is blocking.

1> sp_lock
2> go

The results of sp_lock will reveal what kind of locks, and what object is being locked.

From there we look at sysobjects with the objectid from sp_lock.

1> select * from sysobjects where id = [objectid]
2> go

Now you know exactly:
1. Which task is blocking which
2. What those tasks are, including who is running them on which machine
3. Which resource is being blocked

That should give you the information you need to start thinking about how to avoid this problem.