1. Registered User
Join Date
Jul 2008
Posts
10

can any tell me what value this formula is. is it kb or page sizes.

procedure cache size = (Max# of concurrent users) * (4 + Size of largest plan) * 1.25 is this value of this formula in kilobytes or 2k pages.

if its kilobtypes then my calucations are correct otherwise im confused.

my current procedure cache size is 41000 2k pages. when i use the formula

160 * 961 * 1.25 = 192,200 so if its kilobytes then its 96100 2k pages. which would sound correct. i cant image that my cache size is out by 151,200

i am on sybase 12.5.03

2. Registered User
Join Date
Jun 2009
Location
South Africa
Posts
33
You can generally work on this formula to calculate the required procedure cache size ( as you had it ) :

Upper limit = (Max # of concurrent users) * (Size of largest plan) * 1.25
and
Lower limit = (# of main procedures) * (Average plan size) * 1.25

The size of the plan is in bytes, and you can use this to calculate the largest plan and average for all procs in a specific database:
Code:
select name = convert(varchar(30),name),
pages = (count(*) / 8) + 1,
size = ((count(*) / 8) + 1) * 2048,
average = (avg(count(*)/8) +1) * 2048
from sysprocedures sp, sysobjects so
where sp.id = so.id
and so.type in ('P','TR')
group by so.id
order by 2 desc
go
Number of Procs & Triggers:
Code:
select objects=count(*)
from sysobjects
where type in ('P','TR')
go
Code:
name                           pages       size        average
----                           ----------- ----------- -----------
sp_upd_invsecpos                         7       14336        6144
sp_save_optflow                          5       10240        6144
sp_save_transfer                         5       10240        6144
sp_upd_bo_transfer                       5       10240        6144
.
.
sp_save_trroleloc                        2        4096        6144
sp_upd_book_products                     2        4096        6144
sp_upd_book_currencies                   2        4096        6144

objects
-----------
67
So the largest is 14336 bytes :
for 50 concurrent users -
50 * 14336 * 1.25 = 896000 bytes = 875Kb

Smallest size for average of 6144 bytes is :
for 67 procs & triggers total, 15 being used all the time-
15 * 6144 * 1.25 = 115200 bytes = ~113Kb

The default is 7000 pages or ~13.6Mb. In this case, I'l leave it at the higher default value.

The calculation is simply based on the assumption that every 8 lines per proc in sysprocedures is equal to ~2K or 2048 bytes, since the "text" column in syscomments is a varchar(255) field. The actual SQL text for triggers and stored procedures are stored in syscomments.
This formula does NOT depend on server page size and will work for any logical page size.

If you want to fine-tune it, you can verify the actual usage with sp_monitorconfig as well. Take this rather aggressive example :
Code:
exec sp_configure 'procedure cache size'
go
Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
--------------                 -------     ----------- ------------ ---------    ----                 ----
procedure cache size                  7000      558016       256000       256000 memory pages(2k)     dynamic
Code:
exec sp_monitorconfig 'procedure cache size'
go
Name                      Num_free    Num_active  Pct_act Max_Used    Reuse_cnt
----                      ----------- ----------- ------- ----------- -----------
procedure cache size           255987          13   0.01         9718           0
Based on the config value, we have 500Mb or 256000 2K pages assigned to procedure cache. Looking at what is actually being used, we only used a max of 9718 2K pages since the server was started, which is about 19Mb.

If this is the case, reduce the amount of procedure cache that is configured, and rather assign that to existing data caches where it's more likely to be used. An extra 450Mb could easily be removed here and re-assigned.

3. Registered User
Join Date
Mar 2009
Location
Sydney, Australia
Posts
258
I agree, that formula is good for 12.5. keep in mind that as per Sybase doco, 15.0 needs 2 to 6 times whatever you had, whatever was optimal for you in 12.5. In my experience you need at least 4 times; the 6 times is for those who have Dynamic SQL.
Last edited by Derek Asirvadem; 09-01-09 at 21:41.

4. Registered User
Join Date
Jun 2010
Posts
7
Hi, we have a problem with our database. There is loads of stored proc recompilation. We have the traceflag 299 on to help with this. I have manually checked that the only case stored procs are recompiled is when an index is for a table used within the stored proc is recreated or if the temp table has changed structure. Dropping a table doesn't cause recompilation because of the 299 flag I think (which is good). The problem is we can't figure out why so many stored proc are recompiled. Can anyone suggest any good debugging techniques for this? Also this is the figures from
sp_configure "procedure cache size"
Parameter Name Default Memory Used Config Value Run Value Unit Type
procedure cache size 7000 755106 356000 356000 memory pages(2k) dynamic

exec sp_monitorconfig 'procedure cache size'
Name Num_free Num_active Pct_act Max_Used Reuse_cnt
procedure cache size 138688 217312 61.04 222265 0

So loads of memory being used! which is not good. We are on sybase 15. Any suggestions how to lower the number of recompilles will be appreciated. Thank you.

Panos

5. Registered User
Join Date
Jun 2010
Posts
51
[EDIT] Another post already exists [/EDIT]
Last edited by agrawal.meet; 06-18-10 at 08:36. Reason: post already exists

6. Registered User
Join Date
Jun 2010
Posts
7