Perfmon helpful couters

Buffer cache hit ratio :

It is the percentage of data server had in the cache and did not have to read it from the disk.
Buffer cache hit ratio should be more than 90% for a SQL Server

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 (PLE) :

It is the number of seconds the data will stay in the  cache. The ideal value is 300 if less than 300 or between 300-400 the sever will require more memory.


SELECT *,cntr_value as [PLE in secs],cntr_value / 60 as [PLE in mins],

cntr_value / 3600 as [PLE in hours],

cntr_value / 86400 as [PLE in days]

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Page life expectancy'

AND OBJECT_NAME = 'SQLServer:Buffer Manager'