ASE Database forum - Error 1204 - monitorconfig locks shows <1% use

Author

Error 1204 - monitorconfig locks shows <1% use

Ceri Fassnidge

2006-03-05, 8:42 pm

All,

Adaptive Server Enterprise/12.5.3/EBF 12331
ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/64-bit/FBO/Tue Jan 25
08:52:58 2005

We=92ve been experiencing some problems with ASE running out
of
locks during early morning processing. The server is
currently
configured for 22000 locks (but we've been increasing this
value
steadily over the past few weeks). Once we increase the
locks we
have a few days where early morning processing does not run
out
of locks and then we see the 1204 again.

The output from sp_lock and sp_monitorconfig lock don't show
anywhere near 22000 locks being used - in fact
sp_monitorconfig
lock, run at 20 sec intervals over the 2 hour period where
the
problem is seen doesn't show % use as any more than about
2%.

00:00000:00047:2006/03/03 06:00:00.63 server DBCC TRACEON
3604, SPID 47
00:00000:00053:2006/03/03 07:32:13.81 server Error 1204,
Severity 17, State 2 occurred for User 'XXXX'. Client IP
address is 'XXXX'.
00:00000:00053:2006/03/03 07:32:13.81 server SQL Text:
exec sproc
00:00000:00048:2006/03/03 07:32:13.81 server Error 1204,
Severity 17, State 2 occurred for User 'sa'. Client IP
address is 'XXXX'.
00:00000:00048:2006/03/03 07:32:13.82 server SQL Text:
sp_lock
00:00000:00071:2006/03/03 07:32:13.82 server Error 1204,
Severity 17, State 2 occurred for User 'XXXX'. Client IP
address is 'XXXX'
00:00000:00071:2006/03/03 07:32:13.82 server SQL Text:
00:00000:00018:2006/03/03 08:32:16.29 server Error 1204,
Severity 17, State 2 occurred for User 'XXXX'. Client IP
address is 'XXXX'.
00:00000:00018:2006/03/03 08:32:16.29 server SQL Text:
exec sproc

Usage information at date and time: Mar 3 2006 7:32AM.

Name Num_free Num_active Pct_act
Max_Used Num_Reuse
------------------------- ----------- ----------- -------
----------- -----------
number of locks 21808 192 0.87
22187 0
(return status =3d 0)

Usage information at date and time: Mar 3 2006 7:32AM.

Name Num_free Num_active Pct_act
Max_Used Num_Reuse
------------------------- ----------- ----------- -------
----------- -----------
number of locks 21813 187 0.85
22187 0
(return status =3d 0)

Usage information at date and time: Mar 3 2006 7:32AM.
Name Num_free Num_active Pct_act
Max_Used Num_Reuse
------------------------- ----------- ----------- -------
----------- -----------
number of locks 21808 192 0.87
22187 0
(return status =3d 0)

Usage information at date and time: Mar 3 2006 8:32AM.

Name Num_free Num_active Pct_act
Max_Used Num_Reuse
------------------------- ----------- ----------- -------
----------- -----------
number of locks 21805 195 0.89
22187 0
(return status =3d 0)

Usage information at date and time: Mar 3 2006 8:32AM.

Name Num_free Num_active Pct_act
Max_Used Num_Reuse
------------------------- ----------- ----------- -------
----------- -----------
number of locks 21810 190 0.86
22187 0
(return status =3d 0)

Usage information at date and time: Mar 3 2006 8:32AM.

Name Num_free Num_active Pct_act
Max_Used Num_Reuse
------------------------- ----------- ----------- -------
----------- -----------
number of locks 21809 191 0.87
22187 0
(return status =3d 0)

The tables that the sproc access are datarows locked - a
fairly recent change, however the number of locks has more
than doubled since this change occurred.

Does anyone have any ideas what might be going on?
Many Thanks
Ceri

mpeppler@peppler.org

2006-03-05, 8:42 pm

> All,
>
> Adaptive Server Enterprise/12.5.3/EBF 12331
> ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/64-bit/FBO/Tue Jan 25
> 08:52:58 2005
>
> We=92ve been experiencing some problems with ASE running
out
> of
> locks during early morning processing. The server is
> currently
> configured for 22000 locks (but we've been increasing this
> value
> steadily over the past few weeks). Once we increase the
> locks we
> have a few days where early morning processing does not
> run out
> of locks and then we see the 1204 again.
>
>
> The output from sp_lock and sp_monitorconfig lock don't
> show anywhere near 22000 locks being used

Actually, if you look at the max_used column it does show
that approx. 22000 locks were requested at one time:

> Name Num_free Num_active Pct_act
> Max_Used Num_Reuse
> ------------------------- ----------- ----------- -------
> ----------- -----------
> number of locks 21808 192 0.87
>
> 22187 0

Note the 22187 value here.

22000 locks isn't very much, depending on the sort of
processing that is required and whether escalation to table
locks is possible.

I routinely configure 100000 locks (and more) on moderately
busy servers.

Michael

Ceri Fassnidge

2006-03-05, 8:42 pm

Michael,

I see your point, and i can of course continue to increase
the locks, but i'm just concerned that when the errorlog
indicates that its out of locks, the sp_monitorconfig locks
indicates <1% lock usage.

The db is only 5Gb and not heavily used. Prior to changing
the locking mechanism 10,000 locks were more than enough.

Thanks
Ceri

> out
>
> Actually, if you look at the max_used column it does show
> that approx. 22000 locks were requested at one time:
>
>
> 0.87 >
>
> Note the 22187 value here.
>
> 22000 locks isn't very much, depending on the sort of
> processing that is required and whether escalation to
> table locks is possible.
>
> I routinely configure 100000 locks (and more) on
> moderately busy servers.
>
> Michael

Eric McGrane

2006-03-06, 7:19 pm

Ceri Fassnidge wrote:[color=darkred
]
> Michael,
>
> I see your point, and i can of course continue to increase
> the locks, but i'm just concerned that when the errorlog
> indicates that its out of locks, the sp_monitorconfig locks
> indicates <1% lock usage.
>
> The db is only 5Gb and not heavily used. Prior to changing
> the locking mechanism 10,000 locks were more than enough.
>
> Thanks
> Ceri
>
>
>

Ceri,

The problem is that you are not running sp_monitorconfig when the
problem is happening, you are running it after the fact. When the error
occurs the process that was grabbing all of the locks releases what it
has and you see a relatviely idle system from a lock perspective.

You said you changed to datarows locking from another locking scheme,
not sure if it was datapages or allpages, but in any case, datarows
requires more locks to handle every request than either. For every
operation (read or write) you are now locking each row accessed rather
than each page. Assume 100 rows on a page and now you need to grab 100
locks instead of 1.

The number of locks configured is very low for a system using row level
locking. Increase them, the cost is very cheap in terms of memory.

Regards,
E

Sponsored Links