Post reply

Low Proc Cache

  • 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

  • November 7, 2006 at 2:37 am

    #670242

    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

  • November 7, 2006 at 11:32 am

    #670349

    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."

  • November 7, 2006 at 1:00 pm

    #670379

    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.

  • November 8, 2006 at 2:16 am

    #670497

    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

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

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