Friday, December 10, 2010

Sql Server Performance: Does Your Sql Server Machine Need More RAM? Buffer Cache Hit Ratio Can Inform You.

Buffer Cache Hit Ratio counter indicates how often SQL Server goes to the buffer, not the hard disk, to get the data. 
In OLTP applications, this ratio should exceed 90% (most of DBAs says it should exceed 95%), and ideally be over 99%.If your buffer cache hit ration is lower than 90%, you must add RAM modules. If the ratio is between 90% and 99%, then it should be considered as last warning and you must purchase more RAM.
In OLAP applications, the low ratio is acceptable but still it depends on nature of OLAP working style.


Following is the query I like to use to measure buffer cache hit ratio:

SELECT
      (CAST(SUM(CASE LTRIM(RTRIM(counter_name))
                                                WHEN 'Buffer cache hit ratio'
                                                          THEN CAST(cntr_value AS INTEGER)
                                                ELSE NULL END) AS FLOAT) /
      CAST(SUM(CASE LTRIM(RTRIM(counter_name))
                                                WHEN 'Buffer cache hit ratio base'
                                                          THEN CAST(cntr_value AS INTEGER)
                                                ELSE NULL END) AS FLOAT)) * 100
      AS BufferCacheHitRatio

FROM
      sys.dm_os_performance_counters
WHERE
      LTRIM(RTRIM([object_name])) LIKE '%:Buffer Manager' AND
      [counter_name] LIKE 'Buffer Cache Hit Ratio%'        

No comments:

Post a Comment

All suggestions are welcome