none
Buffer cache hit ratio - question

    Question

  • Hi all,

    bchr is a percentage of the number of times a page is available in the RAM (buffer cache) whenever the DB engine needs it. I wonder if this "hit ratio" is only for page that are to be read or also for page that are to be written.

    Wednesday, January 18, 2012 11:17 AM

Answers

  • This is a good blog on this topic

    Use sys.dm_os_performance_counters to get your Buffer cache hit ratio and Page life expectancy counters


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Stephanie Lv Tuesday, January 24, 2012 9:53 AM
    Thursday, January 19, 2012 4:49 AM
  • Hi Andrea,

    The Buffer cache hit ratio is a matrix about the data which is required to be retrieved directly from the buffer cache, not to fetch the data page from disk and put into the buffer cache for use. Assuming that there is 1GB memory available for SQL Server, which has only one user database with100MB, then the Buffer Cache Hit ratio can up to 99% or above, since all of data can be fit into the memory. You can monitor the buffer cache hit ratio by the Performance Monitor, which is under SQL Server:Buffer Manager performance object.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Stephanie Lv

    TechNet Community Support


    This is not what Buffer Cache Hit Ratio is showing at all.  I prove this in my article:

    Great SQL Server Debates: Buffer Cache Hit Ratio

    This counter provides information about the ability of the read ahead mechanism in SQL Server to read pages from disk into cache before the page is actually required by the query processor.  It doesn't mean that the page was originally in cache at the time the query started, it just means that the page was available in cache at the point it was needed, it could have been placed in cache by a recent prefetch in the storage engine.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Stephanie Lv Monday, January 30, 2012 8:25 AM
    Saturday, January 28, 2012 7:02 AM
    Moderator

All replies

  • Hi,

    Mmmm. The buffer hit cache ratio deals with the percent of pages in the buffer cache that did not have to be read from disk. I don't think there would be any difference between if its a normal read or a write since all pages must first be read into memory then updated before finally being written out to the log and data files.

    If you want to update a page SQL will first check the cache, if its not in the cache it will request the page from disk and read it into memory. Once the page is in memory it can be updated.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog or Twitter.
    Please click the Mark as Answer button if a post solves your problem!

    Wednesday, January 18, 2012 11:28 AM
  • Its for the SPs and queries that do everything .Inserts/Updates/Deletes or any other query that the application might fire on the DB ..
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Wednesday, January 18, 2012 12:08 PM
  • Hi Andrea,

    The Buffer cache hit ratio is a matrix about the data which is required to be retrieved directly from the buffer cache, not to fetch the data page from disk and put into the buffer cache for use. Assuming that there is 1GB memory available for SQL Server, which has only one user database with100MB, then the Buffer Cache Hit ratio can up to 99% or above, since all of data can be fit into the memory. You can monitor the buffer cache hit ratio by the Performance Monitor, which is under SQL Server:Buffer Manager performance object.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Stephanie Lv

    TechNet Community Support

    • Proposed as answer by Naomi N Thursday, January 19, 2012 4:48 AM
    • Marked as answer by Stephanie Lv Tuesday, January 24, 2012 9:53 AM
    • Unmarked as answer by Stephanie Lv Monday, January 30, 2012 8:25 AM
    • Unproposed as answer by Stephanie Lv Monday, January 30, 2012 8:26 AM
    Thursday, January 19, 2012 3:34 AM
  • This is a good blog on this topic

    Use sys.dm_os_performance_counters to get your Buffer cache hit ratio and Page life expectancy counters


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Stephanie Lv Tuesday, January 24, 2012 9:53 AM
    Thursday, January 19, 2012 4:49 AM
  • Hi Andrea,

    The Buffer cache hit ratio is a matrix about the data which is required to be retrieved directly from the buffer cache, not to fetch the data page from disk and put into the buffer cache for use. Assuming that there is 1GB memory available for SQL Server, which has only one user database with100MB, then the Buffer Cache Hit ratio can up to 99% or above, since all of data can be fit into the memory. You can monitor the buffer cache hit ratio by the Performance Monitor, which is under SQL Server:Buffer Manager performance object.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Stephanie Lv

    TechNet Community Support


    This is not what Buffer Cache Hit Ratio is showing at all.  I prove this in my article:

    Great SQL Server Debates: Buffer Cache Hit Ratio

    This counter provides information about the ability of the read ahead mechanism in SQL Server to read pages from disk into cache before the page is actually required by the query processor.  It doesn't mean that the page was originally in cache at the time the query started, it just means that the page was available in cache at the point it was needed, it could have been placed in cache by a recent prefetch in the storage engine.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Stephanie Lv Monday, January 30, 2012 8:25 AM
    Saturday, January 28, 2012 7:02 AM
    Moderator
  • Jonathan,

    The article you posted is really helpful to clarify the misunderstanding on Buffer cache Hit Ratio. I appreciate your correction.

    Thanks for your efforts.


    Stephanie Lv

    TechNet Community Support


    Monday, January 30, 2012 8:25 AM