Blocking and detection

What is blocking and how would you troubleshoot it?

Blocking occurs when a process has acquired lock on a set of rows, and another process is trying to acquire a lock on the same set of rows. In such a case, the other process has to wait until the first process finishes its job and releases the lock on the above said rows.

• Use sp_lock procedure to see type of locks acquired by various sessions on the server to find the cause of blocking.
• Problem is hinted by the WAIT status is a lot of rows that are returned as an output of sp_lock stored procedure execution.
• Use sp_who and sp_who2 to return more columns to get more information around the blocking.
• Use DBCC INPUTBUFFER (spid).This will show the last 128 characters of the last T-SQL statement executed from connection referred through spid. This way one can identify the stored procedure or application module that caused blocking.
• To resolve blocking, you can disconnect the connection causing the blocking using KILL command. If this does not solve the problem permanently, then rewrite the stored procedure/module causing the block more efficiently.

sp_lock

sp_who2

select * from master..sysprocesses

(

select spid,cmd,program_name,blocked,waittime,waitresource,lastwaittype from master..sysprocesses WHERE spid in (66,64) and dbid=7

)

to view the last issued statement, select * from ::fn_get_sql(sql handle returned from sysprocesses)