SQL Server Performance

Cach Hit Ratio (Low)

Discussion in 'Performance Tuning for DBAs' started by jamie.downs, Aug 31, 2004.

  1. jamie.downs Member

    Hi,

    I have been monitoring Cache Hit Ratio on our production and test servers and the numbers appear to be very low. i.e. < 20% for all.

    If I use perfmon to monitor the counters none of them are above 12%. I do notice that the Scale defaults to '0.1000000'. Is this correct? or should it be 1.0.

    The following is what I get from 'DBCC CACHESTATS'

    Proc 0.99172912884185815
    Prepared0.99868657498814917
    Adhoc 0.88580746868504423
    ReplProc0.0
    Trigger 0.99991742727664601
    Cursor 0.99409031145368121
    Exec Cxt0.74350200792623555
    View 0.99694902826362908
    Default 0.31481481481481483
    UsrTab 0.85727878211227404
    SysTab 0.0
    Check 0.88059701492537312
    Rule 0.0
    Summary 0.86037615982992499

    So from the above does, Proc 0.99172912884185815 mean 99% or .99%.

    Thanks in advance for any help or advice



    jamie.downs, Aug 31, 2004
    #2

  2. satya Moderator

    How about memory configuration on SQL Server?
    Any other application sharing the server resources?

    Moreover it is also recommended that you reserve the Servers activity to SQL Server use only, i.e. try to use the SQL Server as a dedicated server rather than just using the same for multiple activities.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Aug 31, 2004
    #2

  3. jamie.downs Member

    Hi,

    Thanks for the reply.

    The server is a dedicated server with AWE enabled and Max server memory set to 7GB.
    It is a 4 way box with 2GHz Xeons.
    Win 2k adserver.
    SQL Server SP3.

    Thanks
    Jamie
    jamie.downs, Aug 31, 2004
    #2

  4. Luis Martin Moderator

    Set to 7, means dinamically up to 7?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

    Luis Martin, Aug 31, 2004
    #2

  5. jamie.downs Member

    Okay,
    The Server is set to Dynamically configure SQL Server memory, with Max Server Memory set to 7GB.
    jamie.downs, Aug 31, 2004
    #2

  6. satya Moderator

    How about routine check of DBCCs & database health status?
    How often you reindex the tables?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Aug 31, 2004
    #2

  7. jamie.downs Member

    To be honest I do not do the DBCC's on a regular basis. The larger indexes are rebuilt daily. Along with sp_updatestatistics.

    Am I reading the results correctly and is the Cache Hit Ratio Low?

    Cheers
    jamie.downs, Aug 31, 2004
    #2

  8. Luis Martin Moderator

    0.99 means 99%.
    If performance monitor show <20%, may be is because recompilations.
    Use SQL Profiler to trace Cache Insert events to see what stored procedures are being compiled.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

    Luis Martin, Aug 31, 2004
    #2

  9. jamie.downs Member

    Luis,

    Perf Mon is showing <20 but if you look at my first entry I mention the scale. Should this be the default? or should it be 1.0? If this is the case it looks a lot better?

    Cheers
    Jamie..
    jamie.downs, Aug 31, 2004
    #2

  10. Luis Martin Moderator

    I confused.
    I use Buffer Cache Hit Ratio counter (Performance Monitor).
    Now I have 99.6% (no scale) in average, and running DBCC CACHESTATS I have similars values than you.
    What I'm missing?



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

    Luis Martin, Aug 31, 2004
    #2

  11. jamie.downs Member

    Hi Luis,

    Cach Hit Ratio not Buffer Cache Hit Ratio. Cache Hit Ratio is in 'SQL Server: Cache Manager -> Cache Hit Ratio.

    Isn't Buffer Cache Hit Ratio for data pages?

    I hope this makes more sense.

    Thanks
    Jamie
    jamie.downs, Aug 31, 2004
    #2

  12. Luis Martin Moderator

    Sorry Jamie.
    The scale is correct. Now I have 58% in total and 74% for proc.
    In short: I have more total hits 58% than you, but less hits using DBCC.
    I don't why you have 20%.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

    Luis Martin, Aug 31, 2004
    #2

  13. jamie.downs Member

    Hi Luis,

    It is actually under 9% for total and 10% Procedure. This is pretty rubbish isn't it?

    Do you have any suggestions how I can improve this.

    Thanks
    Jamie.
    jamie.downs, Aug 31, 2004
    #2

  14. Luis Martin Moderator

    I think there to many recopilations.
    Use SQL Profiler to trace Cache Insert events to see what stored procedures are being compiled

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

    Luis Martin, Aug 31, 2004
    #2

  15. jamie.downs Member

    Hi Luis,<br /><br />I just tried tracing this for about half an hour and there are no recompilations.<br /><br />The events I captured were the following:<br /> SP:Recompile<br /> SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tarting <br /> SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtStarting<br /> SP:Completed<br /><br />As suggested by Kalen Delaney. Do you have any other ideas?<br /><br />Thanks for your continued effort.<br /><br />Jamie
    jamie.downs, Aug 31, 2004
    #2

  16. Luis Martin Moderator

    Luis Martin, Aug 31, 2004
    #2

  17. satya Moderator

    http://vyaskn.tripod.com/analyzing_profiler_output.htm for information and analyze the Profiler output.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Aug 31, 2004
    #2

  18. jamie.downs Member

    Hi Satya/Luis,

    Thanks very much for all of your suggestions. I will get back to you both when I have read the documents you have recommended.

    I do have one quick question. Does the 'Cache Hit Ratio' returned by DBCC PERFMON actually mean 'Buffer Cache Hit Ratio'?

    Thanks
    Jamie
    jamie.downs, Sep 1, 2004
    #2

  19. satya Moderator

    Yes, SQL Server will increase the buffer cache as necessary to ensure a high hit ratio. And this behaviour is by design in SQL Sevrer 2000. If SQL Server does not need the RAM, then it will give it up to the operating system. But that would happen only when the OS askes for the same. The OS reseves the memory till someone asks for that.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Sep 1, 2004
    #2

Share This Page