I am working on a Sybase 11 system (Linux). I am not a DBA (we don't have one here at work, to my chagrin). Lately, I have been seeing messages pop up on a console window (likely the window that I start the Sybase server with) stating that:
"There is not enough procedure cache to run this procedure, trigger, or SQL
batch. Retry later, or ask your SA to reconfigure SQL Server with more
I noted that the procedure cache size value in the configuration file is set to 'default'. For the life of me, I cannot seem to find documentation on what the 'default' value is and/or how to increase it (with Sybase 11, I believe it is a percentage value).
Can someone please point me in the right direction? I would appreciate it.
1) Ensure you have good backups and you have tested them before changing anything.
2) Ensure you try everything on a non-production environment before changing anything in production.
Sybase 11 is quite old now (possibly 20 years) and very much out of support, it's probably worthwhile having this database server upgraded to a later version. If you're paying for support, you can download the software and perform the upgrade yourself, though you should really plan for it before simply putting the latest binaries on your server. Sybase 11.x was initially released in 1995 and has subsequently been updated every few years with a major release. I don't have a copy of such an old version lying around so I can't verify any of this before writing, it's all done from memory.
Due to database object changes stored procedures do grow after they have been created. Adding columns, dropping and re-creating indexes causes the procedures to be re-resolved and re-compiled, patching the original query tree, causing it to grow. Exporting the stored procedure text and re-creating the procedure may help. If this is a newly created stored procedure, you may need to add additional memory to the procedure cache.
The 'default' value of stored procedure cache may only be enough to run the system supplied stored procedures. Since Sybase 12.5 the size of the procedure cache is set to an absolute number of pages, before 12.5 it was a percentage of the max memory parameter.
If all the parameters are set to 'default' you will gain significant a performance improvement by changing some. This will allocate more memory to the database server and an amount corresponding to the percentage will be allocated to the procedure cache.
I think (it is 20 years old) running...
sp_configure 'total memory'
will show you how much memory is configured for ASE. The number is in pages which in 11.x will be 2K bytes.
I seem to remember sizing procedure cache to about 20% of the memory allocated to the database server to the procedure cache.
If you've the memory configuration is set to the default size, this will be something like 8MB (Sybase 12.5), you should increase it. To how much depends on your hardware. If the hardware running ASE is dedicated to ASE, sizing to about 80% is about ideal, this leaves sufficient head room for the operating system. If it is not dedicated you need to size appropriately.