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%’        

Advertisements

Posted on December 10, 2010, in Performance Tuning, Sql Server Internals. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: