Sql-server – Why is the page life expectancy 0 (zero) on SQL Server 2005

page-life-expectancysql serversql-server-2005

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

Best Answer

I would start with doing the steps outlined here:

How to rebuild the performance counters of a SQL Server 2005 or 2008 instance

Update August 2015: Above link appears broken. Alternative reference:

Troubleshooting: SQL Server (2005, 2008) Performance Counter Collection Problems (CSS)

Essential steps from the linked resource

Using an elevated administrator command prompt perform the following steps.

  1. Change the path to the BINN directory of the SQL Server instance you desire to correct.
    (Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn)
  2. Execute unlodctr <<REGISTERED SERVER NAME>>
    For example: unlodctr MSSQL$SQL2008 or SQLAgent$SQL2008 ...
  3. Execute lodctr /T:<<perf-sql* matching the counters you desire to load>>
    For example: perf-MSSQL$SQL2008sqlctr.ini or perf-SQLAgent$SQL2008sqlagtctr.ini for SQLAgent. The /T is important to load the SQL Server performance counter provider as a trusted provider.
  4. Cycle the remote registry service:
    net stop "Remote Registry" then net start "Remote Registry"
  5. Force a WMI synchronization using winmgmt /resyncperfctr "<<PID>>"
    where PID is the process id of the WinPriv.exe (you can get this from Task Manager)

Additionally, from this resource, the following may also be required:

  • Make sure that the correct security permissions have been granted to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009
    registry key:

    1. Grant Full Control permissions to the Creator Owner account.
    2. Grant Full Control permissions to the Administrators account.
    3. Grant Read permissions to the SQL Administrators on that box/Node.
    4. Grant Full Control permissions to the System account
Related Question