Log Shipping – NORECOVERY vs. STANDBY mode

Buffer cache hit ratio

This means what is the percentage that SQL Server had the data in cache and did not have to read the data from disk. Ideally you want this number to be as close to 100 as possible.

In order to calculate the Buffer cache hit ratio we need to query the sys.dm_os_performance_counters dynamic management view. There are 2 counters we need in order to do our calculation, one counter is Buffer cache hit ratio and the other counter is Buffer cache hit ratio base. We divide Buffer cache hit ratio base by Buffer cache hit ratio and it will give us the Buffer cache hit ratio.
Here is the query that will do that, this query will only work on SQL Server 2005 and up.

   1: 1.SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS BufferCacheHitRatio

   2: 2.FROM sys.dm_os_performance_counters  a

   3: 3.JOIN  (SELECT cntr_value,OBJECT_NAME 

   4: 4.    FROM sys.dm_os_performance_counters  

   5: 5.    WHERE counter_name = 'Buffer cache hit ratio base'

   6: 6.        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME

   7: 7.WHERE a.counter_name = 'Buffer cache hit ratio'

   8: 8.AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

Page life expectancy

Now let's look at Page life expectancy.

Page life expectancy is the number of seconds a page will stay in the buffer pool, ideally it should be above 300 seconds. If it is less than 300 seconds this could indicate memory pressure, a cache flush or missing indexes.

Here is how to get the Page life expectancy

   1: 1.SELECT *

   2: 2.FROM sys.dm_os_performance_counters  

   3: 3.WHERE counter_name = 'Page life expectancy'

   4: 4.AND OBJECT_NAME = 'SQLServer:Buffer Manager'