On a production server, you want to minimize the procedure reads from disk. When a user needs to execute a procedure, Adaptive Server should be able to find an unused tree or plan in the procedure cache for the most common procedures. The percentage of times the server finds an available plan in cache is called the cache hit ratio. Keeping a high cache hit ratio for procedures in cache improves performance.
The formulas in Figure 10-2 suggest a good starting point.
Figure 10-2: Formulas for sizing the procedure cache
If you have nested stored procedures (for example, A, B and C)—procedure A calls procedure B, which calls procedure C—all of them need to be in the cache at the same time. Add the sizes for nested procedures, and use the largest sum in place of “Size of largest plan” in the formula in Figure 10-2.
The minimum procedure cache size is the smallest amount of memory that allows at least one copy of each frequently used compiled object to reside in cache. However, the procedure cache can also be used as additional memory at execution time, such as when an ad hoc query uses the distinct keyword which uses the internal lmlink function that will dynamically allocate memory from the procedure cache. Then the create index will also use the procedure cache memory and can generate the 701 error though no stored procedure is involved.
For additional information on sizing the procedure cache see“Using sp_monitor to measure CPU usage”.
