Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008

    Unanswered: Procedure Cache Size

    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. #2
    Join Date
    Jun 2009
    South Africa
    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
    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:
    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 =
    and so.type in ('P','TR')
    group by
    order by 2 desc
    Number of Procs & Triggers:
    select objects=count(*) 
    from sysobjects
    where type in ('P','TR')
    name                           pages       size        average     
    ----                           ----------- ----------- ----------- 
    sp_upd_invsecpos                         7       14336        6144 
    sp_save_optflow                          5       10240        6144 
    sp_save_spreadflow                       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 
    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 :
    exec sp_configure 'procedure cache size'
    Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type       
    --------------                 -------     ----------- ------------ ---------    ----                 ----       
    procedure cache size                  7000      558016       256000       256000 memory pages(2k)     dynamic
    exec sp_monitorconfig 'procedure cache size'
    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. #3
    Join Date
    Mar 2009
    Sydney, Australia
    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.
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

  4. #4
    Join Date
    Jun 2010
    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.


  5. #5
    Join Date
    Jun 2010
    [EDIT] Another post already exists [/EDIT]
    Last edited by; 06-18-10 at 08:36. Reason: post already exists

  6. #6
    Join Date
    Jun 2010

    The answer

    Thank you for all the posts!. I don't know what happened, never used that forum before but I already gave the answer to my question in a previous post but can't see it now.

    Anyway, the problem is that there is a bug in Sybase 15 and when you have traceflag 299 on and you are doing a left outer join with temp tables then it recompiles! I have no idea what happens if traceflag is off, no rights to change it. We checked in 12.5 and it doesn't happen there and raised it with Sybase, they accepted it as a bug in the end. I am hoping that Sybase will provide a fix soon as this is having a bad impact on the whole system.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts