Using RING_BUFFER_RESOURCE_MONITOR to troubleshoot SQL Server memory pressure

Receiving a regular  " There is insufficient system memory in resource pool 'default' to run this query"  message in the SQL Server Error logs, leading to    unavailability of sufficien resources to connect.  

This query supplied by Jonathan Kehayias   is a great way to start assessing if there is SQL Server memory presure .

RESOURCE_MEM_STEADY
No memory issues. Memory low condition  indicating  Windows is ok with the new memory level achieved)

RESOURCE_MEMPHYSICAL_LOW
Windows is running low on memory and SQL Server must return some memory !!

RESOURCE_MEMPHYSICAL_HIGH
Windows has spare memory and SQL Server   , if needed , can take some more memory

IndicatorsSystem > 0   then this means that the memory situation was server-wide.

The IndicatorsProcess value means that it was a specific process which ran into the memory condition and can be one of 3 values:

1 = High Physical Memory
2 = Low Physical Memory
3 = Low Virtual Memory

SELECT 
    EventTime,
    record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') as [Type],
    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/ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess,
    record.value('(/Record/ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem
FROM (
    SELECT
        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') AS tab
ORDER BY EventTime DESC

Often when the error log message " There is insufficient system memory in resource pool 'default' to run this query"  appears - there may be insufficient resource to allow a user connection to the SQL Server to troubleshoot .  If you have sufficient privileges use the SQL Sever - Dedicated Administrator Connection - DAC   to get access and attempt gather some information on what processes are consuming resources within SQL Server.

If you are attempting to identify other information sources to identify external memory pressure - review the application logs . Here is some Powershell to execute on the server:

Review the Application Event log for application-related memory issues.

Here is a sample PowerShell script to query the System and Application Event logs for the keyword "memory". Feel free to use other strings like "memory" for your search:

Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*resource*"
Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*resource*"