Results 1 to 4 of 4

Thread: SP Recompiles

  1. #1
    Join Date
    Jun 2010
    Posts
    7

    Unanswered: SP Recompiles

    Hi,

    we have a problem with our Sybase databases. 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 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

  2. #2
    Join Date
    Jun 2010
    Posts
    1
    Hello,

    I guess you must have checked for the with recompile flag for procs. sp will be recompiled every time if this is mentioned. You may try to remove unnecessary ones by verifying cost difference of execution plans.

    Another choice is to move the creation of temp tables and index to new proc; and calling this from the original one. The SQL that gets recompiled will be very less compared to the current method. The whole original sp wont be recompiled, only smaller one with table & index creation statements will be recompiled.

    Do let me know if this helps!

    Cheers!!

  3. #3
    Join Date
    Jun 2010
    Posts
    7

    Post recompiles

    Thanks! You are not going to believe this but if you have a outer join between 2 tables then you get a recompile!! It doesn't happen with permanent tables or with normal joins. This is on v.15, tomorrow someone with access will check on 12.5. That's a bug and we are raising with Sybase (very poor of them).

    Panos

  4. #4
    Join Date
    Jun 2010
    Posts
    7

    sp_recompile

    I forgot to say that this bug occurs when traceflag 299 is on. I can't say if it happens with the flag switched off cause I can't change it in our systems. It works fine in verison 12.5 (we are 15) no recompiles, the funny thing is that Sybase wouldn't even accept it as a bug! Bless them they did in the end.

Posting Permissions

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