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
- Keep transaction as short as possible
- Use locking hints
- Follow best practices for Database Maintenance regularly
- Improve Query performance so it executes in seconds
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
- SP_WHO2 and find the blkby column from output for blocking details
- Use SP_Lock to find out lock acquired by blocking query
- Select * from sysprocesses where blocked <>0
- You can get the blocking SQL Statement using DBCC INPUTBUFFER(SPID)
- SP_WHO2 SPID