Page Life Expectancy Health

Page Life Expectancy tells you how long a page stays in the buffer pool before being reused - but what is a healthy number? This script reads the Buffer Manager performance counters, computes a PLE target from the total pages in the pool (assuming 300 seconds of life per quarter of the pool), and reports your current PLE against that target as a health percentage. Anything under 100% suggests memory pressure.

--Buffer Manager; Page life expectancy; <All>
--Buffer Manager; Target pages; <All>


DECLARE @PERF_COUNTER_RAWCOUNT			DECIMAL SET @PERF_COUNTER_RAWCOUNT			= 65536			--Absolute Meaning.
DECLARE @PERF_COUNTER_LARGE_RAWCOUNT	DECIMAL SET @PERF_COUNTER_LARGE_RAWCOUNT	= 65792			--Absolute Meaning.

SELECT
	[Object],
	[Counter],
	[Instance],
	[Object] + '; ' + [Counter] + '; ' + [Instance] AS CounterPath,
	Value
INTO
	#Metrics
FROM
(
	SELECT
		LTrim(RTrim(SubString(PC.[object_name], CharIndex(':', PC.[object_name]) + 1, 256))) AS [Object],
		LTrim(RTrim(PC.[counter_name])) AS [Counter],
		LTrim(RTrim(Replace(PC.[instance_name], '_', ''))) AS [Instance],
		CAST(PC.[cntr_value] AS DECIMAL(38, 16)) AS [Value]
	FROM
		sys.dm_os_performance_counters AS PC
	WHERE
		(
			PC.cntr_type = @PERF_COUNTER_RAWCOUNT
			OR PC.cntr_type = @PERF_COUNTER_LARGE_RAWCOUNT
		)
		AND (
			PC.[object_name] LIKE '%:Buffer Manager%'
		)
) AS FormattedMetrics

DECLARE @TotalPages Decimal(18, 2)
DECLARE @PageLifeExpectancy Decimal(18, 2)
DECLARE @PLETarget Decimal(18, 2)

SELECT @TotalPages = Value FROM #Metrics WHERE CounterPath = 'Buffer Manager; Total pages;'
SELECT @PageLifeExpectancy = Value FROM #Metrics WHERE CounterPath = 'Buffer Manager; Page life expectancy;'
SET @PLETarget = ((((@TotalPages * 8.0) / 1024.0 / 1024.0) / 4.0) * 300.0)

SELECT
	@PageLifeExpectancy as PLE,
	@PLETarget as [PLE Target],
	Cast(CASE WHEN @PageLifeExpectancy > @PLETarget THEN 100 ELSE (@PageLifeExpectancy / @PLETarget) * 100.0 END as Decimal(16, 2))
	AS [PLE Health %]

DROP TABLE #Metrics