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
Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s