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.