Tracing blocking

[andreis]

Hi:
What would be the most efficient way to trace processes causing blocking?
Ideally, we’d like to have something that would gather info on what’s causing blocking, and maybe even to have a tool to cancel a process if it’s blocked for more than, say, a minute. Profiler seems to be too resource consuming and not that obvious to use for block hunting. What wise people do in this situation?
Thanks.
Andrei

 

[mrdenny]

If you want to simply scan the server every minute or so and kill any offending threads that have been blocking for several seconds or more, you can do a select off of the master.dbo.sysprocesses table.

Just make sure that when you do your kills, you only kill the spid at the top of the tree. IE if a spid is blocking another process, but it is also being blocked, then don't kill that process.

This query should show you all the spids that are being blocked, and who is blocking them.

select spid, waittime, blocked
from master.dbo.sysprocesses
where blocked <> 0
	and waittime > 10000
	and spid > 50

Like I said above though, don't kill a spid that is being blocked and blocking another spid.
IE. If spid 51 is blocking Spid 60, and Spid 60 is blocking Spid 53, killing spid 60 won't help. Spid 53 will still be blocked by spid 51. Killing spid 51 will free this up.

However the more important thing that killing spids that are blocking other spids, is to find out why the are locking the table.

Blocking is something that the SQL Server does to keep the data safe while people are using the data. Some indexing, and other performance tooning might be helpfull.

Denny

 

[andreis]

Denny, thanks, is it possible to get also a name of the blocking/blocked view or stored procedure based on your query and spid?
Andrei

 

[mrdenny]

you can use dbcc inputbuffer (spid) to get the first 255 characters of the command being run.

Denny

 

[andreis]

Denny, thanks a million, what a great help you are.
Andrei

 

[mrdenny]

no problem

Denny