Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
weird, double checked your code on my prod server and it seems to work fine. –  DForck42 Jun 7 '12 at 17:59
    
Are you rebuilding indexes or updating statistics when you're finding a zero PLE? Just a guess. –  Thomas Stringer Jun 7 '12 at 18:02
    
@Shark maybe with the -x how can I see? and what is the -x parameter. –  ttomsen Jun 7 '12 at 18:49
    
as per MSDN link the -x just disables cpu and buffer hit ratio. I can see buffer hit ratio, so it's not the -X –  ttomsen Jun 7 '12 at 18:55
    
@Shark Nope, we are not rebuiling indexes or statistics at the time I check the PLE counter. We do those at night. –  ttomsen Jun 7 '12 at 18:56
show 1 more comment

1 Answer

I would start with doing the steps outlined here: How to rebuild the performance counters of a SQL Server 2005 or 2008 instance

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.