Tuesday, July 19, 2011

Find Page Life Expectancy (PLE) value for default SQL Server instance

SELECT cntr_value AS [Page Life Expectancy], @@servername AS Server
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND [object_name] = 'SQLServer:Buffer Manager'

Note: If you get cntr_value is less than 300 then, that means

An average page life expectancy of 300 is 5 minutes. Anything less could indicate memory pressure or missing indexes, or a cache flush.