If you’ve ever logged into your SQL Server database only to be greeted by greeted by hundreds or thousands of active spids all of which are being blocked there’s an easy way to identify which blocker is the lead blocker by using the query below. In some cases there may be multiple lead blockers, this indicates that there are several spids running which aren’t being blocked by any other processes. To find the one causing the most harm by looking at the last_batch, cpu, and physical_io.
select blocker.spid, last_batch, cpu, physical_io
from master.dbo.sysprocesses blocker
inner join master.dbo.sysprocesses victim on blocker.spid = victim.blocked
where blocker.blocked = 0
Should be:
ReplyDeleteselect blocker.spid, blocker.last_batch, blocker.cpu, blocker.physical_io
from master.dbo.sysprocesses blocker
inner join master.dbo.sysprocesses victim on blocker.spid = victim.blocked
where blocker.blocked = 0