SQL Server 2016: RING_BUFFER_RESOURCE_MONITOR memory information

Doing some additional memory work.  This is a great post from Jonathan Kehayias(@SQLPoolBoy on Twitter) on an information source that isn't discussed often.


Identifying External Memory Pressure with dm_os_ring_buffers and RING_BUFFER_RESOURCE_MONITOR
https://www.sqlskills.com/blogs/jonathan/identifying-external-memory-pressure-with-dm_os_ring_buffers-and-ring_buffer_resource_monitor/

I grabbed one of the relevant XML records, to see what other good stuff might be in there.

<Record id="13" type="RING_BUFFER_RESOURCE_MONITOR" time="209467780">

  <ResourceMonitor>

    <Notification>RESOURCE_MEMPHYSICAL_HIGH</Notification>

    <IndicatorsProcess>0</IndicatorsProcess>

    <IndicatorsSystem>1</IndicatorsSystem>

    <IndicatorsPool>0</IndicatorsPool>

    <NodeId>0</NodeId>

    <Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>

    <Effect type="APPLY_HIGHPM" state="EFFECT_ON" reversed="1">209397985</Effect>

    <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>

  </ResourceMonitor>

  <MemoryNode id="0">

    <TargetMemory>257792432</TargetMemory>

    <ReservedMemory>480359608</ReservedMemory>

    <CommittedMemory>257792432</CommittedMemory>

    <SharedMemory>0</SharedMemory>

    <AWEMemory>0</AWEMemory>

    <PagesMemory>249898840</PagesMemory>

  </MemoryNode>

  <MemoryRecord>

    <MemoryUtilization>100</MemoryUtilization>

    <TotalPhysicalMemory>268434996</TotalPhysicalMemory>

    <AvailablePhysicalMemory>4601664</AvailablePhysicalMemory>

    <TotalPageFile>272039476</TotalPageFile>

    <AvailablePageFile>7497188</AvailablePageFile>

    <TotalVirtualAddressSpace>137438953344</TotalVirtualAddressSpace>

    <AvailableVirtualAddressSpace>136957561248</AvailableVirtualAddressSpace>

    <AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace>

  </MemoryRecord>

</Record>

On my system, I didn't see any values for SharedMemory, AWEMemory, or AvailableExtendedVirtualAddressSpace.  MemoryUtilization and TotalPhysicalMemory seem like items I can forgo for now.
 
I started with Jonathan's query as a base, and after some bumping around, I worked it into this query which gathers the stuff from these XML records other than the 5 items mentioned up there.  Took me a little while to decide how to flatten out the three Effect nodes per record.  And then what really took a while for me to figure out is that each NodeId gets its own ring_buffer_address, so to uniquely identify these XML records it takes Record ID and ring_buffer_address or NodeId.

--based on a Jonathan Kehayias query found at... 
--https://www.sqlskills.com/blogs/jonathan/identifying-external-memory-pressure-with-dm_os_ring_buffers-and-ring_buffer_resource_monitor/ 

;WITH rb_XML AS
(SELECT ring_buffer_address, DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime, 
        CONVERT (xml, record) AS record
 FROM sys.dm_os_ring_buffers
 CROSS JOIN sys.dm_os_sys_info
 WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'),
rb_record AS
(SELECT rb_XML.EventTime,
        record.value('(/Record/@id)[1]', 'int') AS record_id,
        record.value('(/Record/ResourceMonitor/NodeId)[1]', 'int') AS [NodeId],
        record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') AS [Type],
        record.value('(/Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [IndicatorsProcess],
        record.value('(/Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [IndicatorsSystem],
        record.value('(/Record/ResourceMonitor/IndicatorsPool)[1]', 'int') AS [IndicatorsPool],
        record.value('(/Record/MemoryNode/TargetMemory)[1]','bigint') AS [TargetMemory_Kb],
        record.value('(/Record/MemoryNode/ReservedMemory)[1]','bigint') AS [ReservedMemory_Kb],
        record.value('(/Record/MemoryNode/CommittedMemory)[1]','bigint') AS [CommittedMemory_Kb],
        record.value('(/Record/MemoryNode/PagesMemory)[1]','bigint') AS [PagesMemory],
        record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [Avail_Phys_Mem_Kb],
        record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [Avail_VAS_Kb],
        record.value('(/Record/MemoryRecord/TotalPageFile)[1]','bigint') AS [TotalPageFile_Kb],
        record.value('(/Record/MemoryRecord/AvailablePageFile)[1]','bigint') AS [AvailablePageFile_Kb]
 FROM rb_XML),
rb_effect AS
(SELECT record.value('(/Record/@id)[1]', 'varchar(50)') AS record_id,
        rb_XML.record.value('(/Record/ResourceMonitor/NodeId)[1]', 'int') AS [NodeId],
        elem.value('(@type)[1]', 'varchar(50)') AS effect_type,
        elem.value('(@state)[1]', 'varchar(50)') AS effect_state, 
        elem.value('(@reversed)[1]', 'int') AS effect_reversed,
        elem.value('.', 'bigint') AS effect_value
 FROM rb_XML
 CROSS APPLY record.nodes('/Record/ResourceMonitor/Effect') effect(elem)),
rb__APPLY_LOWPM(record_id, NodeId, APPLY_LOWPM__state, APPLY_LOWPM__reversed, APPLY_LOWPM__value) AS
(SELECT rb_effect.record_id, NodeId, effect_state, effect_reversed, effect_value 
 FROM rb_effect
 WHERE effect_type = 'APPLY_LOWPM'),
rb__APPLY_HIGHPM(record_id, NodeId, APPLY_HIGHPM__state, APPLY_HIGHPM__reversed, APPLY_HIGHPM__value) AS
(SELECT rb_effect.record_id, NodeId, effect_state, effect_reversed, effect_value 
 FROM rb_effect
 WHERE effect_type = 'APPLY_HIGHPM'),
rb__REVERT_HIGHPM(record_id, NodeId, REVERT_HIGHPM__state, REVERT_HIGHPM__reversed, REVERT_HIGHPM__value) AS
(SELECT rb_effect.record_id, NodeId, effect_state, effect_reversed, effect_value 
 FROM rb_effect
 WHERE effect_type = 'REVERT_HIGHPM')
SELECT EventTime,
       rb_record.Record_id,
       rb_record.NodeId,
       [Type],
       [IndicatorsProcess],
       [IndicatorsSystem],
       [IndicatorsPool],
       [TargetMemory_Kb],
       [ReservedMemory_Kb],
       [CommittedMemory_Kb],
       [PagesMemory],
       [Avail_Phys_Mem_Kb],
       [Avail_VAS_Kb],
       TotalPageFile_Kb,
       AvailablePageFile_kb,
       APPLY_LOWPM__state,
       APPLY_LOWPM__reversed,
       APPLY_LOWPM__value,
       APPLY_HIGHPM__state,
       APPLY_HIGHPM__reversed,
       APPLY_HIGHPM__value,
       REVERT_HIGHPM__state,
       REVERT_HIGHPM__reversed,
       REVERT_HIGHPM__value
FROM rb_record
JOIN rb__APPLY_LOWPM ON rb_record.record_id = rb__APPLY_LOWPM.record_id AND rb_record.NodeId = rb__APPLY_LOWPM.NodeId
JOIN rb__APPLY_HIGHPM ON rb_record.record_id = rb__APPLY_HIGHPM.record_id AND rb_record.NodeId = rb__APPLY_HIGHPM.NodeId
JOIN rb__REVERT_HIGHPM ON rb_record.record_id = rb__REVERT_HIGHPM.record_id AND rb_record.NodeId = rb__REVERT_HIGHPM.NodeId
ORDER BY rb_record.record_id, rb_record.EventTime 
OPTION (MAXDOP 1);