Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2008-03-18 : 12:30:03
|
| I have a 32 bit SQL 2005 EE clustered installation with 10GB of physical memory and AWE enabled. Our monitoring tool, Spotlight, is reporting the Procedure Cache to be 384MB and a Hit Rate of 75% on a fairly regular basis. Sometimes the Procedure Cache increases to 495MB and a Hit Rate of 82%.(1) With 2005 can the Procedure Cache be increased?(2) What is the max size of Procedure Cache?(3) How do I increase the Hit Rate to a higher percentage?I do not encounter the issue on any other SQL Server installation, however this is our only cluster.DBCC PROCCACHEnum proc buffs = 64889 num proc buffs used = 1135 num proc buffs = 1135 active proc cache size = 2896 proc cache used = 364 proc cache active = 364Thanks, Dave |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-21 : 19:44:54
|
| 1. add more memory, but procedure cache can't use memory above 3gb range on 32-bit machine.2. less than 3gb in your case, that's for sure. In fact, sql has its own formula for procedure size based on amount of physical memory.3. same as 1. |
 |
|
|
|
|
|
Procedure Cache Hit Rate is Low - Need Advice