Today, I will show you How to Use sys.dm_os_performance_counters to get your Buffer cache hit ratio. 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.
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value,OBJECT_NAME

FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'

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
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.