Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Buffer catch Hit ration Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 12:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 8:06 AM
Points: 167, Visits: 414
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
Post #1350210
Posted Monday, August 27, 2012 12:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 42,462, Visits: 35,522
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 2008, MVP
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

Post #1350214
Posted Monday, August 27, 2012 12:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 8:06 AM
Points: 167, Visits: 414
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 :
Monitor GLEEMASTER-50.17.199.171-MSSQL [IF-gleemaster.com_MSSQL-DB-server_1433]
Attribute Health
Reasons Health of GLEEMASTER-50.17.199.171-MSSQL is critical


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

Thanks
Satish
Post #1350218
Posted Monday, August 27, 2012 1:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 42,462, Visits: 35,522
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 2008, MVP
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

Post #1350222
Posted Monday, August 27, 2012 1:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:29 PM
Points: 459, Visits: 1,889
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
Post #1350229
Posted Monday, August 27, 2012 4:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 8:06 AM
Points: 167, Visits: 414
Hi,

Thank you Gila & Chandan...


---
Satish
Post #1350310
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse