Identify the SPID at the head of the blocking chain. Most blocking problems happen because a single process holds locks for an extended period of time. This usually causes a chain of blocked processes, similar to a slow moving automobile causing a backup on the freeway. Identify the head of the blocking chain from SQL Enterprise Manager by clicking CurrentActivity on the Server menu and observing the Object Locks tab. Alternatively you can use the following example query, which should return one row for each SPID at the head of a blocking chain. See also Knowledge Base article Q122485, "Identifying SPID Responsible for Lock Chain."
create procedure sp_blocker
as
declare @statement char(255)
if exists (select * from sysprocesses where spid in (select blocked from sysprocesses))
select spid,status,loginame=substring(suser_name(suid),1,12),
hostname=substring(hostname, 1, 12), blk=convert(char(3),blocked),
dbname=substring(db_name(dbid),1,10),cmd, waittype
from sysprocesses
where spid in (select blocked from sysprocesses)
and blocked = 0
else
select "No blocking SPIDs found!"
Find what query the blocking SPID is running by running DBCC INPUTBUFFER (spid), where spid is the blocking SPID. Alternatively you can use SQL Enterprise Manager by clicking Current Activity on the Server menu and double-clicking on the SPID to show the input buffer. Save this information.
Find the type of locks the blocking SPID is holding by running sp_lock, or querying master..syslocks. Save this information. Below is an example query. Alternatively you can use SQL Enterprise Manager.
select spid, syslocks.type, locktype=name, table_id=id, page
from syslocks, master.dbo.spt_values v
where syslocks.type=v.number
and v.type='L'
and (syslocks.type & 256)=256
and spid=<blocking spid number>
Sometimes it's necessary to use queries instead of SQL Enterprise Manager because some types of tempdb blocking problems can prevent running queries that use temp table operations. Using direct queries gives the control necessary to avoid this. An example of a query that results in temp table operations is sp_lock, which does an ORDER BY.
Find the transaction nesting level and process status of the blocking SPID. Save this information. This is essentially the same number as @@TRANCOUNT, but can be determined from outside the SPID by using the command DBCC PSS. Example syntax:
dbcc traceon(3604) /* return subsequent DBCC output to client rather than errorlog */
go
SELECT SUID FROM SYSPROCESSES WHERE SPID=<blocking SPID number>
go
DBCC PSS (suid, spid, 0) /* where suid is from above, and spid is the blocking spid number */
go
Note The DBCC PSS statement and the format of the PSS structure are undocumented and unsupported. Their discussion in this article is solely intended to aid customers in diagnosing specific types of blocking problems in their existing systems. The DBCC PSS command should not be incorporated in any existing applications. Microsoft may either discontinue or alter the DBCC PSS command and the PSS structure in future versions of SQL Server.
In the returned information, note pxcb->xcb_xactcnt=n, where n is the @@TRANCOUNT value for the SPID. This shows the transaction nesting level for the blocking SPID, which in turn can explain why it is holding locks. For example, if the value is > 0, then the SPID is in the midst of a transaction, in which case it's normal that it retain any exclusive locks acquired. Note also pstat=n, where n indicates the internal status of the SPID. This can indicate why the blocking SPID may itself be waiting for certain events.
You can also determine whether any long-term open transaction exists in the database by using DBCC OPENTRAN(database_name).
By examining the above four pieces of information, you can usually determine the cause of most blocking problems. Following is a discussion of how to use this information to identify and resolve some common blocking scenarios. See also Knowledge Base article Q125770 "Locking Behavior of Updates and Deletes in SQL Server."