Monday, April 5, 2010

Is your Procedure Cache too big?

Lots of folks have made procedure cache much bigger as they have gone to ASE 15 because... well, it needs a lot more procedure cache than 12.5 did. But, did you give it too much? Can that memory be better used elsewhere?

Here's one quick way to carefully titrate it down:

Sybase recommends incrementally decreasing procedure cache by 200-250MB at a time and then monitoring behavior over time with sp_sysmon.
Before decreasing procedure cache, note the following values in relation to "Procedure Requests": "Procedures Read from Disk" and "Procedures Removals". If you notice these two values increasing significantly in relation to "Procedure Requests", then you should back out the last decrease in procedure cache and keep the size where it is.

Enjoy,

Jeff

No comments:

Post a Comment