Low Proc Cache

Question

Post reply

Low Proc Cache

Scalability Doug

SSCertifiable

Points: 5351
More actions
November 7, 2006 at 1:23 am

#75089

I'm at a loss. Our production server had 8 GB RAM and had high latching, low page life expectancy (<200 sec) and a low proc cache hit ratio (75%). WE just increased memory to 16GB RAM and now page life expectancy is up (4000 sec) and latching has been reduced but the proc cache hit ratio has dropped even lower (55%). My counters are showing that the procedure cache is 80 MB and ad hoc queries are about 40% of that. I know that ad hoc queries have been practicallt eliminted. Why is proc cache so low???

TIA, DAB

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

DAB

Now that you're up to 16GB, have you removed the /3GB switch from your boot.ini file?

You should expect to see a low cache hit ratio soon after the server is restarted as not all queries may have had a chance to have their execution plans cached. What is performance like from an end user's point of view - does it appear to have improved or stayed the same?

John

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 2

8 Gb of RAM with /3GB, now 16 Gb of RAM. Why would you remove the /3GB switch ? Am I missing something here ???

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 3

Well now that things have had 8 hours + to stabilize I rechecked the proc cache. It is up to 330 MB of which Ad Hoc queries occupy about 220 MB. Evidently our developers did not remove as much inline sql as had thought. Of the number of batches submitted since midninght 7500 of 11,000 statements are still inline SQL as opposed to procs. Latch counts are still a little high for my tastes but latch waits are down and page life expectancy is till much higher than before the memory ( 300 before the memory and over 9000 after). I'll have to be content until I can get rid of the inline SQL.

Thanks.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

I'm sure the 16GB /3GB issue is documented in lots of other places, but this is the first one I found:

http://www.sql-server-performance.com/awe_memory.asp

John