Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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.

Former Member
Not what you were looking for? View more on this topic or Ask a question