Procedure Cache Hit Ratio -

  • We have a sql server with 2.0g of RAM for collecting small amount of data daily. Monitoring the Procedure Cache Hit Ratio, the counter is around 82%~89%. However according to the performance monitors, average memory utilization is below 50%. My question is why the Proc Chace Hit Ratio is around 82%~89% when not all the Memory being used? Could someone explain this?

    thanks

  • tran008 (7/19/2010)


    We have a sql server with 2.0g of RAM for collecting small amount of data daily. Monitoring the Procedure Cache Hit Ratio, the counter is around 82%~89%. However according to the performance monitors, average memory utilization is below 50%. My question is why the Proc Chace Hit Ratio is around 82%~89% when not all the Memory being used? Could someone explain this?

    thanks

    Stored procedure recompiles, adhoc queries, executing with RECOMPILE etc... Look at the whitepapers below for more details.

    Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

    Plan Caching in SQL Server 2008

    [font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

  • It is due to procedure cache bloating as sankar mentioned.

    Thank You,

    Best Regards,

    SQLBuddy

  • thanks, since this is a third party software, changing the code is a bit of a problem. Will addition 2.0g of RAM, since it is cheap to get, help at all?

  • I'd say no. Your issue is that the procs are not in the cache, but it's not necessarily a memory problem. It's much more likely to be a coding issue. Just a guess, the app uses ad hoc sql or an ORM tool or something along those lines, right? You're lucky you're getting 80% cache hits.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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