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
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.
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).
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.