Skip to Content
avatar image
Former Member

Blocking on tempdb*..sysobjects - why is number of open indexes so high ?

We had an odd situation where we had a lot of processes blocked by an insert into a temp table (haven't seen this for a long time!)

     insert #monoFun (Date, x,y)

     select ....

Locks were

  Object - tempdb_xxxx.sysobjects

  Lock Level - ROW

  Lock Type- next key

  LockState - Requested

I haven't see the "next key" lock type ? What's this lock mean ?

We couldn't see what was causing it

object_stats agrees with this

Object Name: tempdb_cpgb..sysobjects

             (dbid=7, objid=1, lockscheme=Datarows)

  Row Locks     SH_ROW                  UP_ROW                  EX_ROW

  ----------    ----------              ----------              ----------

  Grants:              369                     363                     770

  Waits:                 1                       0                       0

  Deadlocks:             0                       0                       0

  Wait-time:          9409 ms                    0 ms                    0 ms

Checking sp_monitorconfig  we found "open indexes" reports we'd reached the max of 10,000 so we've increased it.

We now get this.

Name                      Num_free    Num_active  Pct_act Max_Used    Reuse_cnt   Instance_Name

------------------------- ----------- ----------- ------- ----------- ----------- ------------------------------

number of open indexes           4971       10029  66.86        10039       14439 NULL

We have a total of 20,000 indexes in the system.

Is the "number of open indexes" the number of indexes being used ? or just the number that have been used over time ?

What would cause the number active to decrease because it does increase and decrease.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Sep 12, 2014 at 04:59 PM

    Num_active is the number of descriptors that are active (assigned to an index, though not necessarily being used by any user connections) at this moment.  This number goes down when indexes are dropped.  Most of the fluctuation will be due to temporary work tables.

    Reuse is the number of times ASE has had to scavenge a descriptor that was active but not currently being used because there wasn't a free descriptor.

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 12, 2014 at 05:26 PM

    "Next key" and "infinity locks" are used for DOL locked tables under isolation level 3 - e.g. datarows (which sysobjects and other system tables are datarows locked).   There should be a description of this in the P&T Locking manual or the Transact SQL Users Guide, but essentially what happens is if you do a query that affects (for example) all the "B*'s", ASE will lock the first "C" row to prevent another "B" from being added.   Soooo....I expect that for some reason the transaction that is inserting into #temp is running in ISO 3 - perhaps to make sure the select portion is consistent.   I would check to see if that is really necessary as a first step.    Often times, this happens with java apps when someone calls a proc and the default java JDBC setting is chained mode...kinda different as it simply means that every DML starts a transaction, but if the connection is in a connection pool and someone didn't clean up after an iso3 query (via set), then the insert into #temp would be in a iso3 transaction.   However, a pure insert into #foo would not have held the lock on sysobjects - it likely was a create table #foo sometime earlier (or select/into) - that is why I mentioned chain mode.   The insert *might* be the current statement that was being run by the spid holding the lock, but the real cause was the DDL in tran (check to see if that is on as well).   One other possibility is that the coder might have done something  like:

    if exists (select * from sysobjects where name like '#monoFun%')

    insert into #monoFun select * from ...

    That like clause in the IF statement would be an exact match for the type of query that would drive a "next key" lock under ISO 3.....and would be what I would suspect.    Still, not sure why it would be inside a transaction unless in chained mode, etc.......

    Add comment
    10|10000 characters needed characters exceeded