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?
views:
222answers:
3
+6
A:
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
gbn
2010-02-18 18:48:16
+1: Simple and effective.
John Sansom
2010-02-18 18:56:16
+1, but it is: master.dbo.sysprocesses
KM
2010-02-18 18:58:36
@KM How about next time, edit the answer and correct it...? Thanks.
Josh Stodola
2010-02-22 22:21:02
@KM: or master..sysprocesses or master.sys.sysprocesses If you omit the schema, it works on SQL 2000+ plus
gbn
2010-02-23 07:35:21
A:
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
John Sansom
2010-02-18 18:57:54