procedure cache is reaching 100%
One of Sybase sever running on older version having this abnormal issue.procedure cache size usage is growing gradually upto 100% suddenly in every 2-3 days and server got hung finally. application updation is managed by batch job in every end of day.
We didn't find anything abnormal and this was happened sometime in non-business hours when there is no activity running on server also
Any advice on this type of problem
1> sp_monitorconfig "all"
2>
Name Num_free Num_active Pct_act Max_Used Num_Reuse
------------------------- ----------- ----------- ------- ----------- -----------
additional network memory 472860 1563904 76.78 1563904 0
audit queue size 100 0 0.00 0 0
disk i/o structures 0 10382 100.00 10485 0
heap memory per user 4096 0 0.00 0 0
max cis remote connection 3200 0 0.00 0 0
max memory 351520 8037088 95.81 8037088 0
max number network listen 4 1 20.00 1 0
max online engines 0 8 100.00 8 0
memory per worker process 796 228 22.27 228 0
number of alarms 50 10 16.67 14 0
number of aux scan descri 400 0 0.00 50 0
number of devices 16 14 46.67 14 0
number of dtx participant 500 0 0.00 0 0
number of java sockets 817 0 0.00 0 0
number of large i/o buffe 6 0 0.00 1 0
number of locks 298624 1376 0.46 29781 0
number of mailboxes 128 128 50.00 128 0
number of messages 64 0 0.00 0 0
number of open databases 9 16 64.00 16 0
number of open indexes 7807 4193 34.94 4228 0
number of open objects 32826 8134 19.86 13600 0
number of remote connecti 20 0 0.00 3 0
number of remote logins 20 0 0.00 3 0
number of remote sites 9 1 10.00 2 0
number of sort buffers 32767 0 0.00 99542 0
number of user connection 590 210 26.25 257 0
number of worker processe 128 0 0.00 0 0
partition groups 1024 0 0.00 0 0
permission cache entries 25 7 21.88 10 870651
procedure cache size 2335419 2664581 53.29 2676618 0
size of global fixed heap 300 0 0.00 0 0
size of process object he 3000 0 0.00 0 0
size of shared class heap 3072 0 0.00 0 0
size of unilib cache 200816 720 0.36 720 0
txn to pss ratio 12800 0 0.00 0 0
(return status = 0)
Here is cache details as below
1> sp_helpcache
2>
Cache Name Config Size Run Size Overhead
------------------------ ------------- ---------- ----------
default data cache 4096.00 Mb 4096.00 Mb 432.16 Mb
Memory Available For Memory Configured
Named Caches To Named Caches
-------------------- ----------------
4096.01 Mb 4096.00 Mb
------------------ Cache Binding Information: ------------------
Cache Name Entity Name Type Index Name Status
---------- ----------- ---- ---------- ------
(return status = 0)
1> sp_cacheconfig
2>
Cache Name Status Type Config Value Run Value
------------------------------ --------- -------- ------------ ------------
default data cache Active Default 4096.00 Mb 4096.00 Mb
------------ ------------
Total 4096.00 Mb 4096.00 Mb
==========================================================================
Cache: default data cache, Status: Active, Type: Default
Config Size: 4096.00 Mb, Run Size: 4096.00 Mb
Config Replacement: strict LRU, Run Replacement: strict LRU
Config Partition: 8, Run Partition: 8
IO Size Wash Size Config Size Run Size APF Percent
-------- --------- ------------ ------------ -----------
2 Kb 491520 Kb 0.00 Mb 3072.00 Mb 10
16 Kb 209664 Kb 1024.00 Mb 1024.00 Mb 10
1> sp_configure "procedure"
2>
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
allow procedure grouping 1 0 1 1 switch dynamic
procedure cache size 3271 10898890 5000000 5000000 memory pages(2k) dynamic
(1 row affected)
(return status = 1)
Thanks
April