How to find out what SQL queries are being blocked

问题:

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?

回答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

回答2:

Activity Monitor

回答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