cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member182259
Contributor
0 Kudos

"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.......

Former Member
0 Kudos

> 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.


We don't ever use isolation level  3 - at least we don't explicitly.


> the connection is in a connection pool and someone didn't clean up after an iso3 query

Why don't the drivers automatically do this after using the connection.


I'm guessing the problem is with the connection pool and some other proc implicitly having a isolation level of 3. We've had numerous problems with connection pool been left in a bad state.


MS-SQL handles this really well - connection is reset after its returned to the pool - why can't SAP do the same thing.  I know the SAP/Sybase hasn't invested very much in .net; is this likely to improve in the future ?


>  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 ...

I've checked this and there's no checking of sysobjects in this proc.

Thanks

former_member182259
Contributor
0 Kudos

If the app server is using the wrong driver (as is often the case with ASE - people mistakenly assume the jTDS driver shipped with many app servers is ASE compliant - it isn't), that could be an issue.  You might want to check what specific driver is being used.

However, remember, there are several ways to set an application context - programmatically and via set command.   If you set an application context programmatically, by standards, the connection pool is supposed to clean up after you (and typically does).   For example, if you create a fully prepared statement, when you release the connection back to the connection pool, the app server will tell the connection to drop all the prepared statements.

However, some programmers will issue language commands such as "set <optionname> on".   Since this is often issued using a standard language command interface, the connection has no knowledge that it was done.

But a "next key" lock definitely indicates isolation level 3.   You can verify via select * from master..sysoptions where SPID=###   where ### is the number of the spid of interest.

Former Member
0 Kudos

Thanks

I should have been more explicit in saying we're using ADO .net and not JDBC.

We're on old ADO drivers (ie those without the working set of BCP api's) so have to upgrade.

Are the saying the ADO drivers should clean up the connection pool after use ? If so, we don't see this happening (temp tables, current database and other information is left lying around)

FYI, MS-SQL ADO drivers call a proc sp_reset_connection which clears out a lot of issues to clean up the connection - including dropping temp tables - we're having to implement something similar ourselves.

> But a "next key" lock definitely indicates isolation level 3. You can verify via

> select * from master..sysoptions where SPID=###   where ### is the number of the spid of interest.

What should I look for in this list  ? I have 118 entries per spid but can't see anything to indicate isolation level.

former_member182259
Contributor
0 Kudos

My bad....you can only see it via dbcc pss(suid,spid) - look for pisolation=#  ....should be about line 196 in the output....to see the output, you will need to do dbcc traceon(3604) first.....   For example:

dbcc traceon(3604)

go

dbcc pss(1,24)

go

dbcc traceoff(3604)

go

....obviously you will need to look up both the suid and spid before execution.   Area of interest will be:

pstringsize=50  pbulkbatchsize=0  pbulkarraysize=50
pretstat=0   pexecstep=0x0000000000000000   pcurstmt=0x000000008007F000   prepxdes=0x0000000000000000
psqlstatus=0 pfetchstatus=-1 pcursorrows=0
pplocks.lcstmtlocks=0x0000000031F13B10 pplocks.lcstmtl3locks=0x0000000031F13B20
pxlocks.lcstmtlocks=0x0000000031F13B30 pxlocks.lcstmtl3locks=0x0000000031F13B40

prepthreshold=0

ppllcfg_stat=0   pisolation=1
pexec_maxthread=1   pexec_maxthread_ext=0
pexec_maxscanthread=1   pexec_maxscanthread_ext=0

....vs. with isolation level three:

pstringsize=50  pbulkbatchsize=0  pbulkarraysize=50
pretstat=0   pexecstep=0x0000000000000000   pcurstmt=0x0000000080081000   prepxdes=0x0000000000000000
psqlstatus=0 pfetchstatus=-1 pcursorrows=0
pplocks.lcstmtlocks=0x0000000031F13B10 pplocks.lcstmtl3locks=0x0000000031F13B20
pxlocks.lcstmtlocks=0x0000000031F13B30 pxlocks.lcstmtl3locks=0x0000000031F13B40

prepthreshold=0

ppllcfg_stat=0   pisolation=3
pexec_maxthread=1   pexec_maxthread_ext=0
pexec_maxscanthread=1   pexec_maxscanthread_ext=0

I will check with engineering/support if our current ADO drivers have sp_reset_connection....don't see it in my default server build, but then I don't use ADO much.

Former Member
0 Kudos

Thanks - I couldn't see anything to reset the connection - but if you find out I also raised the query here

former_member182259
Contributor
0 Kudos

Apparently, it isn't part of the ADO.NET spec....so we don't have anything.   Comment from primary support engineer was that a proc such as sp_reset_connection also would be source of contention/problems and a better implementation might be just to implement a reset function in driver that issued a set of commands specified by app.... 

Former Member
0 Kudos

It may not be part of the explicit spec but surely the default action of a connection in the connection pool is that when its returned to the pool for re-use its available in the "correct" state.

In the world of grids, being able to re-use a connection is essential.

Your argument seems to be that its up to us to ensure applications are developed correctly and the state of connection is always left in a good state. However, the argument from the developers here is that people make mistakes and MS-SQL doesn't have these issues so why not use that ?

I agree, I don't understand why sp_reset_connection would be a source of problems ? MS-SQL manages to do it without any issues.

former_member182259
Contributor
0 Kudos

While I partially agree with what you are saying, the rest of it is starting to get argumentative and this is not the forum for such.

I can only comment that I see both sides - and just encourage you to open a feature request (I suggest one as well - but if you do via standard SAP support channels, then it will look like more than one person....which always helps in prioritization).

Former Member
0 Kudos

Didn't mean for it to be getting argumentative.

I'll pass the request through the departments here.

Thanks

former_member188958
Active Contributor
0 Kudos


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.