When database server is experiencing slowness one of the very first things we check are blocks. Blocking can be described as
Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. On SQL Server, blocking occurs when one SPID holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first SPID locks the resource is very small. When it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance. – MSDN
When such blocking persists for a longer duration and there are many another SPID waiting directly or indirectly or the lock to be released - one finds many blocks on the server. This degrades performance by causing waits. The first step is to verify what is causing the blocks.
We can use sp_who2 active, sys.dm_exec_requests , sysprocesses or sp_lock to get information about the locks. When there are large number of blocks on a server and one needs to figure out what is the root SPID that is causing all this – We are looking for a lead blocker. Finding lead blocker in a chain of blocks is tedious process. Which is why I normally use below two scripts to get the required data :-
select a.spid, a.blocked, b.spid, b.blocked from sysprocesses a inner join sysprocesses b on b.spid= a.blocked and b.blocked = 0 and a.spid<> a.blocked
Once you have the lead blocker SPID , you can see the statement using DBCC INPUTBUFFER (SPID) or use the below script to get the SQL Text of lead blocker in a single go.
SELECT spid ,sp.status ,loginame = SUBSTRING(loginame, 1, 12) ,hostname = SUBSTRING(hostname, 1, 12) ,blk = CONVERT(char(3), blocked) ,open_tran ,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10) ,cmd ,waittype ,waittime ,last_batch ,SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2 ) FROM master.dbo.sysprocesses sp LEFT JOIN sys.dm_exec_requests er ON er.session_id = sp.spid OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) as qt WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND blocked = 0
There is a very good article on Microsoft Support portal regarding How to monitor blocking in SQL Server 2005 and in SQL Server 2000
