We've run out of procedure cache on a server and am trying to estimate how much procedure cache we might need.
In the last few days we've increased the procedure cache from 9Gb to 14Gb.
procedure cache size = 120 users * 170 * 1.25 = 25,500 pages or k ? (if pages is that 50Mb)
Assuming a stack tree of 4 times this (which we think would be high) - thats still 100Mb - well short of the 9Gb of procedure cache we ran out of.
I've checked dbcc memusage and get some interesting stats
Proc Cache Memory: 14801 Mb 7578315 15520389120
Database Id: 26
Object Id: 108328915
Object Name: cache2Application
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Bytes lost for alignment 0 (Percentage of total: 0.000000)
Number of plans: 58
Size of plans: 185.869293 Mb, 194898080.000000 bytes, 95816 pages
Bytes lost for alignment 1033298 (Percentage of total: 0.530174)
This shows that a plan here is 3Mb and we have 58 plans so I'd guess a maximum stack tree of 12Mb to 15Mb per process.
procedure cache size = 120 users * 15Mb * 1.25 = 2Gb
Thats still way below the 9Gb we ran out of but perhaps more realistic.
Is there some way we can get all the procedure cache usage ? memusage annoying only shows the top 20.