Skip to Content
0
Former Member
Jul 15, 2015 at 01:03 PM

How to size procedure cache

423 Views

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.

Looking at

SyBooks Online

I get

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

Version: 1

Uid: 1

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.

so

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.


Mike