Buffer catch Hit ration

  • Hi All,

    I am getting a alerts Buffer catch Hit ration 100% and when i check buffer manager details through resource monitor found some below details.

    SQL SERVER BUFFER MANAGER :

    Buffer catch hit Ratio 100.000

    Free list stalls/sec 0.000

    Free pages 1321.000

    Page life expectancy 4136748.000

    Page reads/sec 0.000

    I am using Win server 2008 with 8GB RAM, let me know how do i fix this issue.

    Thanks & Regards

    Satish

  • There's nothing there that looks like it needs fixing.

    Buffer cache hit ratio - higher is better, so 100 is good, not a problem that needs fixing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Gila,

    Thanks for response but i am getting below alert from server

    This is an auto generated e-mail triggered because of the following entities being monitored :

    MonitorGLEEMASTER-50.17.199.171-MSSQL [IF-gleemaster.com_MSSQL-DB-server_1433]

    AttributeHealth

    ReasonsHealth of GLEEMASTER-50.17.199.171-MSSQL is critical

    Let me advice what shall i response to my boss....

    Thanks

    Satish

  • That the alert is set up in error, buffer cache hit ratio of 100 is good, not bad.

    Double-check what the alert was actually complaining about. It is was buffer cache hit ratio, you can ignore it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • As Gail suggested above, the higher is this ratio the better. It simply means that your memory is having sufficient amount of data in the cache which will reduce the physical I\O operations.

    However, if you are really interested to go deeper into this analysis, create your small warehouse for analysis of memory,disk and cpu counters over the period of time(say 2 weeks) so that you can have a fair bit of idea on the overall processing. you may use DMVs or the good old perfmon but make sure that during this period you do not reboot sql server or use dbcc commands to flush the cache because logically it will impact the analysis negatively.

    Thanks

    Chandan

  • Hi,

    Thank you Gila & Chandan...

    ---

    Satish

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

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