SupportSQL

Blocking is resolved here by tracing the blocking/blocked spids with activity monitor (206 & 67 below) to the locked object with sp_lock (677577452) then to the object (Revision table) to determine possible causes, here we find that large indexes and multiple indexes are causing long latencies, blocking and timeouts during table updates. An index analysis will show which indexes are not being used and can therefore be eliminated, reducing update times and lowering blocking issues and lock wait timeouts. Low use indexes can also be exchanged for statistics which do not update at the time of data updates, therein lowering SQL Server internal update processing.

—by Alek Kirstein – SupportSQL.com