Skip to Content
0
Former Member
Sep 12, 2014 at 04:48 PM

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

199 Views

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.