good statistic for blocking

We are having an occasional problem occur where a
process will not stop blocking.

We are trying to trace the problem, but in the interim,
I would like to set up an alert that notifies me when a
process has been blocking for too long.

Are any of the lock wait times good statistics to use
for such an alert? If not, is there anything else I could
look at from the alert level?

If I had to, I could periodically create a table of
sysprocess spids that are at the top of blocking chains,
then test for a spid that lingers. I'm hoping I can
avoid this and use the built-in monitoring instead, though.

Thanks!
GeoffWe're also monitoring open transactions kept for a specific threshold, depending on an app. In addition, look at sp_monitor system stored procedure and you may get some ideas on what and how it can be accomplished.|||What information do you have so far concerning the locking issue - how long is it blocking, how did you discover that blocking was occuring ... ?|||Thanks, rdjabarov. Active Transactions is a good idea, since they
don't ever seem to build up higher than 4 or 5, if today's performance
monitor results can be trusted.

rnealjr:

When we encounter the problem, the blocking processes seem like
they would continue blocking perpetually. The only thing I can do to
solve the situation is to go in and kill the offending process. I am
trying to address the reason this occurs, but in the meantime I need
to fix it by brute force.

We are running a Java application that accesses the data using
a CORBA-based middle tier (Versata, in case you've heard of it).
The CORBA objects use the MS JDBC driver to hit the database,
which is MS SQL 2000.

I just tried upgrading the driver to sp2 and SQL to sp3a last night.
Things were going well this morning, but I think I caused some
trouble when I ran DBCC SHOWCONTIG while following a performance
tuning doc.

Anyway, I figured I'd show an example of an offending process,
obtained by using sp_blocker_pss80:

========================================

SPID: 144
STATUS: sleeping
BOLCKED: 0
OPEN_TRAN: 1
WAITRESOURCE: 8:1:269596
WAITTYPE: 0x0000
WAITTIME: 0
CMD: AWAITING COMMAND
LATWAITTYPE: PAGEIOLATCH_SH
CPU: 30
PHYSICAL_IO: 26
MEMUSAGE: 26
LASTBATCH: 2004-01-14 08:48:28.003
LOGINTIME: 2004-01-14 08:41:00.490
NETADDRESS 000000000000
NET_LIBRARY: TCP/IP
DBID: 8
ECID: 0
KPID: 0
HOSTNAME: spnvls00.na01.crl.com
HOSTPROCESS: 0
LOGINNAME: ilims
PRGRAMNAME:
NT_DOMAIN:
NT_USERNAME:
UID: 1
SID: 0xCDEB103BDA944A418B51092B9253F78D...
SQL_HANDLE: 0x0000000000000000000000000000000000000000
STMT_START: 0
STMT_END: 0

========================================

The thing I find weird, although a more experienced eye may not, is
that it's sleeping and "AWAITING COMMAND", yet it still has 1 open
transaction. It seems to me like this kind of situation could cause
a lingering block.

Thanks again!|||99% of the time it IS the main reason for blocking, along with long running transactions.|||When this happens again, you can use enterprise manager. Go to management -> current activity -> locks/processid - this will help you locate the process that is blocking and will show all the lock information associated with that process. Open that process - that will show up to 255 characters what action is causing the block which will help you locate what/where the problem is. Always make sure that you refresh the "current activity" first - otherwise, you might be looking at old process info. Once you have the information you need, then kill the process.|||Below is the script I've been using since 6.5 version to identify the blocking chain:

set statistics time off
set nocount on
set statistics io off
if charindex('6.5', @.@.version) = 0 begin
exec ('set statistics profile off')
end
--go
select
spid,
BlockedBy=blocked,
Status=convert(char(10), status),
DBName=convert(varchar(20), db_name(dbid)),
Host=convert(varchar(20), hostname),
Program=convert(varchar(40), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
WHERE Exists (
select
spid,
blocked,
status,
DBName=convert(varchar(20), db_name(dbid)),
Host=convert(varchar(20), hostname),
Program=convert(varchar(40), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses victim (NOLOCK)
where blocker.spid = victim.blocked)
UNION
select
spid,
blocked,
Status=convert(char(10), status),
DBName=convert(varchar(20), db_name(dbid)),
Host=convert(varchar(20), hostname),
Program=convert(varchar(40), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
where blocked != 0
ORDER BY blocked, spid

begin tran
create table #tmp (spid int null, BlockedBy int null)
commit tran
insert #tmp (spid, BlockedBy) select spid, BlockedBy from (
select
spid,
BlockedBy=blocked,
DBName=convert(varchar(30), db_name(dbid)),
Host=convert(varchar(30), hostname),
Program=convert(varchar(50), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
WHERE Exists (
select
spid,
blocked,
DBName=convert(varchar(30), db_name(dbid)),
Host=convert(varchar(30), hostname),
Program=convert(varchar(50), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses victim (NOLOCK)
where blocker.spid = victim.blocked)
UNION
select
spid,
blocked,
DBName=convert(varchar(30), db_name(dbid)),
Host=convert(varchar(30), hostname),
Program=convert(varchar(50), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
where blocked != 0 ) x
ORDER BY blockedby, spid
if @.@.rowcount > 0 begin
set nocount on
declare @.spid int, @.cmd varchar(255), @.cmd1 varchar(255)
select @.spid = min(spid) from #tmp where BlockedBy = 0
while @.spid is not null begin
select @.cmd = 'dbcc inputbuffer(' + convert(varchar(25), @.spid) + ')'
set @.cmd1= '!!!!!!!!!!!!!!!!!INPUTBUFFER information for blocking SPID ' + convert(varchar(25), @.spid) + ':'
print ''
print '************************************************* ***************************************'
print @.cmd1
print '************************************************* ***************************************'
exec (@.cmd)
-- print '************************************************* ***************************************'
print ''
select @.spid = min(spid) from #tmp where BlockedBy = 0 and spid > @.spid
end
print '************************************************* ****************************************'
print ''
print 'LIST OF AFFECTED PROCESSES'
print '**************************'
select @.spid = min(spid) from #tmp
while @.spid is not null begin
select @.cmd = 'dbcc inputbuffer(' + convert(varchar(25), @.spid) + ')'
select @.cmd1= 'INPUTBUFFER informtion for SPID ' + convert(varchar(25), @.spid) + ':'
print @.cmd1
exec (@.cmd)
print ''
select @.spid = min(spid) from #tmp where spid > @.spid
end
end else
print 'No blocking detected!'
go
drop table #tmp
go