In SQL Server 2008, there is one important performance counters that can be used to monitor memory performance. The SQL Server Hit Ratio shows the ratio of user requests served by data cache im memory.
Here is the description about Buffer Cache Hit Ratio from MSDN
Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server.
Since accessing data in memory(buffer cache) is much faster than accessing the same information from I/O subsystem, ideally, we always want to data is from memory instead of I/O.A desired value for cache hit ratio should be over 90 percent.
One simple way to get the value of buffer cache hit ratio is by querying the sys.dm_os_performance_counters dynamic management view. You need to get two counters from this view in order to calculate the Buffer cache hit ratio, one is Buffer cache hit ratio and the other one is Buffer cache hit ratio base. We divide Buffer cache hit ratio by Buffer cache hit ratio base and it will give us the Buffer cache hit ratio.
Here is the query to get the value of Buffer Cache Hit Ratio
SELECT ROUND(CAST(A.cntr_value1 AS NUMERIC) / CAST(B.cntr_value2 AS NUMERIC),6) AS Buffer_Cache_Hit_Ratio
FROM (
SELECT cntr_value AS cntr_value1
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Buffer cache hit ratio'
) AS A,
(
SELECT cntr_value AS cntr_value2
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Buffer cache hit ratio base') AS B;
In addition to running T-SQL, you can get the Buffer Cache Hit Ratio through the Windows Performance Monitor. After you launched the Performance Monitor from the Window Start Menu (Administrative Tools ).
To let the Performance Monitor displays the value of Buffer Cache Hit Ratio, you need to add the counter. In below screen capture, you will see Buffer Cache Hit Ratio is under SQLServer::Buffer Maanger section. After you added the counter, you will see the graphical presentation of the Buffer Cache Hit Ratio like below.
To recap, Buffer Cache Hit Ratio is a commonly used parameter to measure the memory performance of the databases but only in SQL Server, but Oracle and DB2 etc. This value provides us an insight whether the database server has been allocated to memory and read operation are done with memory.
