Determine the page life expectancy per NUMA node

Determine the page life expectancy per NUMA node (is it balanced?)

--****************************************************************************************
-- Determine the page life expectancy per NUMA node (is it balanced?)
--****************************************************************************************
-- Versie:  1.0
-- Author:  Theo Ekelmans
-- Date:    2014-01-22
--
--****************************************************************************************

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

SELECT	ple.[NUMA]
		, LTRIM(STR([PageLife_Sec] / 3600)) + ':' + REPLACE(STR([PageLife_Sec] % 3600 / 60, 2), SPACE(1), '0') + ':' + REPLACE(STR([PageLife_Sec] % 60, 2), SPACE(1), '0') AS 'PageLife'
		, CONVERT(decimal(15, 0), opc.[DatabasePages] * 0.0078125) AS 'BufferPool_MB'
		, CONVERT(decimal(15, 0), opc.[DatabasePages] * 0.0078125 / [PageLife_Sec]) AS 'BufferPool_Delta_MB_Sec'
FROM	(	
			SELECT	[instance_name] AS 'NUMA'
					, [cntr_value] AS 'PageLife_Sec'
			FROM	sys.dm_os_performance_counters
			WHERE	[counter_name] = 'Page life expectancy') AS ple
			INNER JOIN (	
						SELECT	[instance_name] AS 'NUMA'
								, [cntr_value] AS 'DatabasePages'
						FROM	sys.dm_os_performance_counters
						WHERE	[counter_name] = 'Database pages')AS opc
			ON ple.[NUMA] = opc.[NUMA]

Leave a Reply

Your email address will not be published. Required fields are marked *