|
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.What is blocking and how would you troubleshoot it? Blocking occurs when two or more rows are locked by one SQL connection and a second connection to the SQL server requires a conflicting on lock on those rows. This results in the second connection to wait until the first lock is released.
Troubleshooting blocking:
- SQL scripts can be written that constantly monitor the state of locking and blocking on SQL Server
- The common blocking scenarios must be identified and resolved.
- The scripts output must be checked constantly,
- The SQL profilers data must be examined regularly to detect blocking.
|
