We have been wanting to know the page life expectancy for our SQL Server. So we look at the counter using Perform. The value is ZERO and never changes. I thought there must be some bug, so I checked the SQL Server DMV query
SELECT [object_name],[counter_name],[cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy'
That also returns zero, all day long.
To make this more interesting, we checked the 'buffer cache hit ratio' counter, with it averaging around 99-100.
So how is the page life expectancy zero while the 'buffer cache hit ratio' is 100?
What are we missing? if it's always zero, it would mean to me that nothing stays in the buffer cache, which seems incorrect if the buffer cache hit ratio is 100?
Thanks in advance