How to find out what SQL queries are being blocked and what's blocking them?

Question

I'm trying to optimize some slow web pages, and my guess is that the problem has to do with SQL blocking (doesn't seem to be a matter of CPU or I/O utilization on the web server or database server). What's the quickest way to find out what queries are getting blocked and what queries are doing the blocking?

Answer 1

SELECT
    p1.SPID AS blockedSPID, p2.SPID AS blockingSPID, ...
FROM 
    master..sysprocesses p1
    JOIN
    master..sysprocesses p2 ON p1.blocked = p2.spid

Remus: Activity monitor may time out under server load with error 1222

The MS KB 224453 has a lot of good stuff

Answer 2

Activity Monitor

Answer 3

How to monitor blocking in SQL Server 2005 and in SQL Server 2000

A very nice Blog post here:

Getting Blocking Info In SQL Server 2005 & 2008 the easy way