best size of procedure cache size?

[Former Member]

here is my dbcc memusage output:

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Memory Usage:

                                   Meg.           2K Blks                Bytes

      Configured Memory:     14648.4375           7500000          15360000000

Non Dynamic Structures:         5.5655              2850              5835893

     Dynamic Structures:        70.4297             36060             73850880

           Cache Memory:     13352.4844           6836472          14001094656

      Proc Cache Memory:        85.1484             43596             89284608

          Unused Memory:      1133.9844            580600           1189068800

So if proc cache is too small? I can put used memory 1133M to proc cache. but as many suggested that proc cache should be 20% of total memory.

Not sure it should be 20% of max memory or Total named cache memory?

[Former Member]

Hi Kent,

Your sp__monitorconfig 'procedure cache size' outputs indicates the followings:-

• current usage of 26+%
• HWM of 787,437 x2K pages, which is 52+%
• re-used count of 746,136, which is 50%

The ASE expert, whom you consulted, is right to say that re-used count should be as close to zero as possible.

Do you have Statement Cache enabled (check 'statement cache size')?

If you have, it may be too small.  ASE would not exceed 'statement cache size' configured when caching new incoming statements even though there may be free space available in the Procedure Cache (with HWM of 52+%, there was 47+% spare at the time), instead, ASE would remove the oldest cached statements from Statement Cache ( and their Light Weight Procedures (LWPs), + query plans ) to free up space - this is probably the reason for the re-used count of 50%.

Best regards,

Raymond

[Former Member]

Sizing procedure cache size is not so easy thing.

From ASE 15.x it procedure cache is used for many modules (not just for compiled stored porcedures).

It is also for statement cache, hash joins and many others.

You can see what is inside by:

select * from master..monProcedureCacheModuleUsage

Sybase recomends to increase procedure cache after upgrade from 12.5.x to 15.x 2-3 times.

If I could suggest you should start even with 500MB size and then monitor it.

[former_member207908]

Hi

Database size: 268288.0 MB

Procedure Cache size is ..

1> sp_configure 'procedure cache size'

2> go

Parameter Name                 Default     Memory Used  Config Value    Run Value         Unit                            Type

------------------------------ ----------- ----------- ----------------------------------------------------------- ---------------------------------------------

procedure cache size           7000          3362132        1494221           1494221         Memory pages(2k)     dynamic

1> sp_monitorconfig 'procedure cache size'
2> go

Usage information at date and time: May 15 2014 11:48AM.

Name                      Num_free    Num_active  Pct_act Max_Used    Reuse_cnt   Instance_Name
------------------------- ----------- ----------- ------- ----------- ----------- ------------------------------
procedure cache size          1101704      392517  26.27       787437      746136 NULL

1> sp_configure 'total logical memory'

2> go

Parameter Name           Default     Memory Used   Config Value      Run Value           Unit                         Type

------------------------------ ----------- ----------- -----------------------------------------------------------------------------------------------------

total logical memory        73728    15624170           7812085             7838533      memory pages(2k)     read-only

I got to know that the oparameter 'Reuse_cnt' should be zero from an ASE expert.

Suggest me if I need to increase the procedure cache with explanation

Thanks

Rajesh

[Former Member]

It looks like you have quite big enough procedure cache.

There is a problem with "Reuse_cnt". You can only reset this by ASE reboot.

So the best way is to make ASE reboot after you increase Procredure Cache Size.

Sometimes high usages of Procedure cache is only when you run some specific admin tasks.

Of course the best thing is when you have Reuse_cnt = 0.

If you have too small Procedure Cache in rare situations you can get 100% CPU utilization.

So you should reboot you ASE and monitor it.

You can always use to monitor procedure cache (what is done there) tables:

master..monProcedureCacheModuleUsage

master..monProcedureCacheMemoryUsage

But also there are some problems related with procedure cache and spinlocks.

http://wiki.scn.sap.com/wiki/display/SYBASE/ASE+Traceflag+758

http://scn.sap.com/docs/DOC-49523

Tomek

[Former Member]

Thanks. but there is no table:

master..monProcedureCacheModuleUsage

master..monProcedureCacheMemoryUsage

so these tables available for which version of ase?

[kimon_moschandreou]

Hello,

the right setting depends mostly on how much do you need, maybe the 85MB are enough, maybe not. dbcc memusage shows more details of your proc cache, also you may use

sp_monitorconfig 'procedure cache size'

to see the max value of proc cache used since last reboot

I would propose to set a bigger value (200MB maybe) and from this starting point decide to increase or decrease it, the 85MB seem not enough to me, however the most important factor here is how much proc cache is required by your workload.

Regards,

Kimon