Procedure cache sizing

On a production server, minimize the number of procedure reads from disk. When a user needs executes 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 5-2 suggest a good starting point.

Figure 5-2: Formulas for sizing the procedure cache

Image consists of two forumulas: Procedure cache size = (Max number of concurrent users) times (4 + size of largest plan) times 1.25  Minimum procedures cache size = (number of main procedures) times (Average plan size)

If you have nested stored procedures—procedure A calls procedure B, which calls procedure C—all of them must be in the cache at the same time. Add the sizes for nested procedures, and use the largest sum instead of “Size of largest plan” in the formula in Figure 5-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 for sorting and query optimization as well as for other purposes. Furthermore, the memory required is based on the type of the query.

Use of sp_monitorconfig to configure procedure cache:

  1. Configure procedure cache to the minimum size as determined above.

  2. Run your normal database load. If you get error 701, increase procedure cache size. Tune the size of the increase to avoid over-allocation. The recommended increase is (128 * (size of procedure cache, in GB)). For procedure cache size less than 1GB, increase in 128MB increments. For procedure cache size greater than 1GB but less than 2GB, increase in 256MB increments, and so on.

  3. Run sp_monitorconfig “procedure cache size” when Adaptive Server has reached or passed the peak load.

  4. If sp_monitorconfig indicates that Max_Used is considerably less than the current value for procedure cache from sp_configure, then procedure cache is over-allocated. Consider reducing the procedure cache size configuration value so that a smaller procedure cache may be allocated during the next restart.

  5. A value other than zero for the Num_Reuse output from sp_monitorconfig also indicates a shortage of procedure cache. If this value increases over a period of time, consider increasing procedure cache size as suggested in step 2 above.