Various memory troubleshooting queries on System Health session files

sys-health-memory-broker.sql

-- get memory broker events from system health file session
IF OBJECT_ID('tempdb..#events') IS NOT NULL
	DROP TABLE #events;

WITH   XmlDataSet AS
(
		SELECT	CAST(xe.event_data  AS xml) AS XMLDATA
		FROM	sys.fn_xe_file_target_read_file('system_health_*.xel',NULL,NULL,NULL) as xe
		WHERE	xe.[object_name] = 'memory_broker_ring_buffer_recorded'
)
SELECT 
       XMLDATA AS EventXML
,      ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as rn -- rely on physical ordering...
INTO   #events
FROM   XmlDataSet AS a;

SELECT
	E.rn AS event_number
--,	((E.rn / 6) % 2)+1 AS pool_id	-- big assumption based on max number of distinct memory brokers per pool...
,	M.MemoryBrokerRB.value('(@timestamp)[1]', 'datetime2') AS timestamp
,   M.MemoryBrokerRB.value('(data[@name="broker"])[1]', 'nvarchar(max)') AS broker
,   M.MemoryBrokerRB.value('(data[@name="notification"])[1]', 'nvarchar(max)') AS notification
,   M.MemoryBrokerRB.value('(data[@name="new_target"])[1]', 'bigint') AS new_target
,   M.MemoryBrokerRB.value('(data[@name="currently_predicated"])[1]', 'bigint') AS currently_predicated
,   M.MemoryBrokerRB.value('(data[@name="currently_allocated"])[1]', 'bigint') AS currently_allocated
,   M.MemoryBrokerRB.value('(data[@name="previously_allocated"])[1]', 'bigint') AS previously_allocated
--,	M.MemoryBrokerRB.query('.') as fullnode
FROM   #events AS E
CROSS APPLY eventxml.nodes('//event[@name="memory_broker_ring_buffer_recorded"]') AS M(MemoryBrokerRB)
ORDER BY event_number;

sys-health-oom.sql

-- get memory broker out of memory events from system health file session
IF OBJECT_ID('tempdb..#events') IS NOT NULL
	DROP TABLE #events;

WITH   XmlDataSet AS
(
		SELECT	CAST(xe.event_data  AS xml) AS XMLDATA
		FROM	sys.fn_xe_file_target_read_file('system_health_*.xel',NULL,NULL,NULL) as xe
		WHERE	xe.[object_name] = 'memory_node_oom_ring_buffer_recorded'
)
SELECT 
       XMLDATA AS EventXML
,      ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as rn -- rely on physical ordering...
INTO   #events
FROM   XmlDataSet AS a

-- event still to be decoded (need an example)
SELECT
	E.rn AS event_number
,	M.MemoryBrokerRB.value('(@timestamp)[1]', 'datetime2') AS timestamp
,	M.MemoryBrokerRB.query('.') as fullnode
FROM   #events AS E
CROSS APPLY eventxml.nodes('//event[@name="memory_node_oom_ring_buffer_recorded"]') AS M(MemoryBrokerRB)
ORDER BY event_number;

sys-health-sp-diag-resources.sql

-- get RESOURCE details in sp_server_diagnostics_component_result event from system health file session
IF OBJECT_ID('tempdb..#events') IS NOT NULL
	DROP TABLE #events;

WITH   XmlDataSet AS
(
		SELECT	CAST(xe.event_data  AS xml) AS XMLDATA
		FROM	sys.fn_xe_file_target_read_file('system_health_*.xel',NULL,NULL,NULL) as xe
		WHERE	xe.[object_name] = 'sp_server_diagnostics_component_result'
)
SELECT 
       XMLDATA AS EventXML
,      ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as rn -- rely on physical ordering...
INTO   #events
FROM   XmlDataSet AS a;

WITH cte
AS
(
	SELECT
		E.rn AS event_number
	,	D.SPDiagnostics.value('(@timestamp)[1]', 'datetime2') AS timestamp
	,	R.ResourceEvent.value('(@lastNotification)[1]', 'nvarchar(max)') AS lastNotification
	,	R.ResourceEvent.value('(@outOfMemoryExceptions)[1]', 'bigint') AS outOfMemoryExceptions
	,	R.ResourceEvent.value('(@isAnyPoolOutOfMemory)[1]', 'bigint') AS isAnyPoolOutOfMemory
	,   R.ResourceEvent.value('(memoryReport[@name="Process/System Counts"]/entry[@description="Available Physical Memory"]/@value)[1]', 'bigint') AS physical_mem_avail
	,   R.ResourceEvent.value('(memoryReport[@name="Process/System Counts"]/entry[@description="Page Faults"]/@value)[1]', 'bigint') AS page_faults
	,   R.ResourceEvent.value('(memoryReport[@name="Memory Manager"]/entry[@description="Target Committed"]/@value)[1]', 'bigint') AS target_mem_committed
	,   R.ResourceEvent.value('(memoryReport[@name="Memory Manager"]/entry[@description="Current Committed"]/@value)[1]', 'bigint') AS current_mem_committed
	--,	R.ResourceEvent.query('.') as fullnode
	FROM   #events AS E
	CROSS APPLY eventxml.nodes('//event[@name="sp_server_diagnostics_component_result"]') AS D(SPDiagnostics)
	CROSS APPLY eventxml.nodes('//event/data[@name="data"]/value/resource') AS R(ResourceEvent)
	WHERE	 eventxml.exist('//event/data[@name="component"]/text[text()[1]="RESOURCE"]') = 1
)
SELECT
	timestamp, lastNotification, outOfMemoryExceptions, isAnyPoolOutOfMemory, page_faults
,	physical_mem_avail / 1024. / 1024 / 1024 AS physical_mem_avail_GB
,	target_mem_committed / 1024. / 1024  AS target_mem_committed_GB
,	current_mem_committed / 1024. / 1024 AS current_mem_committed_GB
FROM	cte
ORDER BY event_number;

Filtering both on object_name when retrieving events with fn_xe_file_target_read_file, and when calling the .nodes() method, is redundant but I left it in the code for those cases when it's more practical to filter on multiple events type earlier when populating the temporary table (e.g. to scan a large number of files only one time).