Post reply

Buffer catch Hit ration

  • August 27, 2012 at 12:20 am

    #393028

    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

  • August 27, 2012 at 12:27 am

    #1530198

    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

  • August 27, 2012 at 12:38 am

    #1530202

    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

  • August 27, 2012 at 1:05 am

    #1530204

    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

  • August 27, 2012 at 1:26 am

    #1530210

    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

  • August 27, 2012 at 4:50 am

    #1530283

    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