Troubleshoot Blocking

Troubleshooting Blocking 

What is blocking? Blocking occurs when one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away. Blocking is not the same thing as a deadlock. 

How to avoid blocking

1. Keep transaction as short as possible


2. Use locking hints


3. Follow best practices for Database Maintenance regularly


4. Improve Query performance so it executes in seconds

Note:

By default, blocking locks do not time out.You can set blocking timeout using below SET statement.

SET LOCK_TIMEOUT timeoutPeriod

 How to find blocking

1. SP_WHO2 and find the blkby column from output for blocking details


2. Use SP_Lock to find out lock acquired by blocking query


3. Select * from sysprocesses where blocked <>0


4. You can get the blocking SQL Statement using DBCC INPUTBUFFER(SPID)


5. SP_WHO2 SPID