Although there is no formula for determining how much procedure cache is best for a particular environment, you can estimate how much is needed to optimize the procedure cache and therefore the performance of SQL Server. The following calculation is a good starting point for sizing the procedure cache:
Procedure Cache = (Maximum Concurrent Users) * (Size of Largest Plan) * 1.25
Note You can use DBCC MEMUSAGE to determine the size of a query plan for a particular procedure.
The following example illustrates another approach to sizing the procedure cache. Consider a SQL Server application environment consisting of 500 stored procedures. Of these, 100 main procedures are used frequently throughout the day, and the other 400 are executed only periodically. To avoid the run-time overhead of regenerating query plans and loading them into the cache, size the procedure cache so that it can hold at least one copy of each main procedure.
To determine the amount of memory needed to store the query plans of the main procedures, use DBCC MEMUSAGE. Then estimate the total size needed by examining a few representative procedures and determining an average plan size. Because each query plan uses at least one page in the procedure cache, the smallest plan is 2K. When you have determined an average plan size, you can estimate the minimum procedure cache by multiplying the average plan size by the number of main procedures.
The minimum procedure cache size is the smallest amount of memory that can allow at least one copy of each frequently used compiled object to reside in the cache. For this example, assume that the main procedures have an average plan size of 15 pages. To allow at least one copy of each of these procedures to reside in the cache, the minimum procedure cache is 1500 pages (1000 * 15 pages).
After you determine the minimum procedure cache size, add an additional amount to allow for concurrent usage, as well as for less frequently used procedures. Although this amount varies, an extra 10 percent is a reasonable starting point.
These are just two approaches to sizing the procedure cache. Depending on the requirements of your specific environment, you might be able to achieve sufficient performance by using a smaller procedure cache than indicated using these methods. If memory resources are limited, be sure to run several tests to determine that the procedure cache that you have calculated is best for your environment.