Cached plans can disappear while the error log stays quiet. The resource monitor ring buffer helps connect that disappearance with recent memory notifications.
Start With the Missing Plans
A smaller cache does not identify its cause. Plans leave through memory pressure, explicit clearing, configuration changes, recompilation, and ordinary eviction. The investigation starts with the time of the disappearance.
I check memory notifications before blaming a procedure for losing its plan. I also keep the notification time beside the cache observation. Without that alignment, two unrelated changes become an attractive story.
This buffer contains resource monitor records retained in memory. It provides a fast diagnostic view when historical monitoring is missing. It does not replace a collection system or prove which session consumed memory.
A cache has no sentimental attachment to yesterday's plans. SQL Server releases allocations when its memory management requires it. Your task is to establish the conditions around that release.
Run the queries using an account with the required diagnostic permission. On SQL Server 2022 and later, these memory views require VIEW SERVER PERFORMANCE STATE. Older versions use VIEW SERVER STATE.
Read Recent Resource Monitor Ring Buffer Records
Start with the raw record and its counter timestamp. Restrict the query to the resource monitor type. Keeping the raw XML available helps explain unexpected nulls in the parsed result.
SELECT TOP (50)
rb.[timestamp] AS RecordedTicks,
TRY_CONVERT(xml, rb.record) AS RecordXml
FROM sys.dm_os_ring_buffers AS rb
WHERE rb.ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR'
ORDER BY rb.[timestamp] DESC;The timestamp represents milliseconds since the computer started. It is not a date, and it is not milliseconds since the SQL Server instance started. Treating it as either produces misleading dates.
The record column contains diagnostic XML. Microsoft does not document its internal schema as a supported contract. Test the paths against the raw record on your instance before building automation around them.
An empty result means no matching entries remain visible in this snapshot. It does not establish that memory pressure never happened. Recent entries replace older entries as the buffer fills.
Capture this output promptly when cache shrinkage is reported. Save the collection time and instance identity alongside it. A screenshot without those details becomes difficult to connect with later evidence.
Convert Ticks Into a Date
Use the current operating system tick counter to estimate the record's age. Subtract that age from the current local server time. Both counters belong to the same computer and use milliseconds.
The calculation below splits the age into minutes and remaining milliseconds. That avoids placing a long uptime difference into one integer millisecond argument. The resulting date remains an approximation from the collection instant.
DECLARE @CapturedAt datetime2(3) = SYSDATETIME();
WITH Records AS
(
SELECT rb.[timestamp], TRY_CONVERT(xml, rb.record) AS RecordXml,
si.ms_ticks - rb.[timestamp] AS AgeMs
FROM sys.dm_os_ring_buffers AS rb
CROSS JOIN sys.dm_os_sys_info AS si
WHERE rb.ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR'
)
SELECT TOP (50)
DATEADD(millisecond, -CONVERT(int, AgeMs % 60000),
DATEADD(minute, -CONVERT(int, AgeMs / 60000), @CapturedAt)) AS ApproximateLocalTime,
RecordXml.value('(Record/ResourceMonitor/Notification/text())[1]', 'nvarchar(100)') AS Notification,
RecordXml.value('(Record/ResourceMonitor/IndicatorsProcess/text())[1]', 'int') AS ProcessIndicators,
RecordXml.value('(Record/ResourceMonitor/IndicatorsSystem/text())[1]', 'int') AS SystemIndicators,
RecordXml.value('(Record/MemoryRecord/AvailablePhysicalMemory/text())[1]', 'bigint') AS AvailablePhysicalMemory
FROM Records
WHERE AgeMs >= 0
ORDER BY [timestamp] DESC;Read the notification first, then compare neighboring records. A low memory notification followed by a recovery notification suggests a changing condition. It does not tell you the duration of every affected query.
The resource monitor ring buffer is useful because it preserves recent transitions. Present memory views show conditions now, which differ from conditions during an earlier notification. Keep both kinds of evidence separate.
Check Windows Memory Now
The operating system view reports total and available physical memory. Its low and high memory signals describe the current Windows condition. They help distinguish external pressure from an isolated SQL Server allocation concern.
SELECT total_physical_memory_kb, available_physical_memory_kb,
total_page_file_kb, available_page_file_kb,
system_low_memory_signal_state,
system_high_memory_signal_state,
system_memory_state_desc
FROM sys.dm_os_sys_memory;
SELECT physical_memory_in_use_kb, locked_page_allocations_kb,
large_page_allocations_kb, memory_utilization_percentage,
available_commit_limit_kb,
process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory;The process view describes SQL Server's process rather than all Windows consumers. Physical memory in use and the process low memory flags answer different questions. Read their units before comparing values.
Do not subtract every displayed allocation from every other allocation. Some columns describe overlapping memory categories rather than independent totals. A neat arithmetic result still needs a valid accounting model.
If Windows pressure is present, inspect other consumers through Windows monitoring. If only process pressure appears, investigate SQL Server memory allocations and its configured limits. These snapshots identify the next investigation, not an automatic setting change.
What was happening outside SQL Server when the plans disappeared? That question matters on shared computers and virtual machines. A database cannot report the complete resource policy of its host.
Interpret Resource Monitor Ring Buffer Indicators
The indicator fields are internal diagnostic values. Retain them beside the notification, but do not invent a permanent decoding table. A change in XML layout can invalidate a parser without changing the underlying condition.
Use notification names as clues, then confirm the surrounding facts. Compare the affected time with workload volume, Windows memory, and configuration changes. Similar timestamps support correlation, while independent measurements support a stronger conclusion.
The resource monitor ring buffer does not list every evicted plan. To investigate a specific statement, capture its cache identity and execution context separately. Query Store supplies another perspective when it was collecting the workload.
A restart removes in-memory diagnostic history. Counter dates from a previous computer boot also cannot be carried into the current calculation. Preserve evidence outside the buffer when repeat incidents need comparison.
Keep local time and UTC labels explicit when comparing saved results. The conversion uses the server's current local clock. Monitoring systems that store UTC need a deliberate conversion before drawing a shared timeline.
A clock correction also affects the estimated calendar time. The underlying tick difference still describes elapsed milliseconds within this boot. Avoid treating a reconstructed wall clock value as an independently recorded timestamp.
Build a Repeatable Capture
Collect the raw records and both memory snapshots together. Name the instance, record the collection time, and retain the complete result. This makes later comparisons less dependent on memory or recollection.
For recurring pressure, schedule a lightweight collection with a retention policy. Keep the documented snapshot columns as your stable foundation. Treat parsed internal XML as supplemental evidence that needs periodic validation.
Review monitoring access because diagnostic records expose operational details. Give the collector only the necessary permissions and protect its saved results. A useful investigation does not require broad application access.
Finally, connect the recent notification with the specific cache change you observed. If the timing does not align, continue checking other eviction causes. A short buffer provides a lead, and the surrounding evidence determines its value.
Related reading on this blog: Detecting Memory Pressure: SQL in Sixty Seconds #186 and 3 Queries to Detect Memory Issues.
A memory notification is not a complete history, it is a recent clue.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
