To caculate how much procedure cache you need, there’s a set of calculations which can be performed;

The upper size = (Max number of concurrent users) * (Size of the largest plan) * 1.25

The lower size =(Total number of procedures) * (average size of query plan) * 1.25

To work out the largest query plan size execute the following query in the main user database;

select max(count(*)/8 +1)

from sysprocedures

group by id

And to find the average size use this;

select avg(count(*)/8+1)

from sysprocdures

group by id

To calculate the total number of stored procdures execute the following query;

select count(*)

from sysobjects

where type=”p”

Max number of concurrent users is found from the result of sp_configure “max number of users”

Say for example the results came out to be 21MB and 3MB a reasonable figure could be 6MB but obviously if you have 21MB for the procedure cache then that is ideal, this canbe achieved by increasing total memory so that 20 % of total cache is 6MB or by altering the percentage of procedure cache out of total cache.