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?
Asked
Active
Viewed 9,795 times
3 Answers
12
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
- 422,506
- 82
- 585
- 676
-
@KM How about next time, edit the answer and correct it...? Thanks. – Josh Stodola Feb 22 '10 at 22:21
-
@KM: or master..sysprocesses or master.sys.sysprocesses If you omit the schema, it works on SQL 2000+ plus – gbn Feb 23 '10 at 07:35
2
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
- 41,005
- 9
- 72
- 84