To help identify blocking in your databases, Microsoft has two separate stored procedures listed on their website (one each for SQL Server 7.0 and 2000) you can use to help identify blocking problems on your SQL Servers. On these pages are scripts you can use to create stored procedures that you can run anytime to help you identify blocking issues.
INF: How to Monitor SQL Server 7.0 Blocking (Q251004)
INF: How to Monitor SQL Server 2000 Blocking (Q271509)
[7.0, 2000] Updated 7-24-2006
*****
One way to help avoid blocking locks is to ensure that your tables have appropriate indexes. This is because the quicker that SQL Server can find the data it is looking for, the less time locks have to be in place, reducing the potential for blocking locks to occur in the first place.
For example, if SQL Server needs to perform an UPDATE to a row, and there is not an appropriate index to quickly find the row that needs to be UPDATED, then a table scan must be performed. While SQL Server is examining every row in the table (which can take a long time if there are many rows), SQL Server has to maintain an UPDATE lock on the entire table during the duration of the table scan, potentially causing much blocking.
Table scans are not the only performance problem. Poorly designed indexes, while perhaps faster than a table scan, can be much slower than a well-designed index, potentially contributing to blocking. It is always in your best interest to optimize indexes so they perform at their maximum. [6.5, 7.0, 2000, 2005] Updated 7-24-2006
*****
Another way to help identify potential blocking issues is to run DBCC OPENTRAN to identify open transactions. This will provide you with the SPID of the open transaction. Next, you can use DBCC INPUTBUFFER to find out what the last command that was run for this SPID. Sometimes, this can help you identify the cause of the blocking. [7.0, 2000, 2005] Updated 7-24-2006
]]>