Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Question Unanswered: 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!
    Geoff

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    We'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.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    What information do you have so far concerning the locking issue - how long is it blocking, how did you discover that blocking was occuring ... ?

  4. #4
    Join Date
    Jan 2004
    Posts
    4
    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!

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    99% of the time it IS the main reason for blocking, along with long running transactions.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Below is the script I've been using since 6.5 version to identify the blocking chain:

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •