SQL Server Internal Memory Pressure
8
votes
1
answer
2366
views
I am trying to identify the cause of internal memory pressure. From what I have learned, Resource Monitor Ring Buffer returns an indicator for internal pressure. For example by using this query,
SELECT Top (1) *
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
The XML results (within the resource monitor tag):
RESOURCE_MEMPHYSICAL_LOW
2
0
0
0
100789
0
In this article by Bob Dorr, shrinking signal from Memory Broker could create internal memory pressure and reflect in resource monitor notification with
IndicatorsProcess = 2 (IDX_MEMPHYSICAL_LOW)
as shown above.
This article by Slava Oks shows another way of checking internal memory pressure with RING_BUFFER_SINGLE_PAGE_ALLOCATOR
. I don't know much about this particular ring buffer because I never seen it in my environment.
SELECT Top (1) *
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SINGLE_PAGE_ALLOCATOR'
The XML result this time:
477
477
31553
31553
...
My question is, which internal processes may be contributing to internal physical memory pressure that will turn on this indicator = 2 (IDX_MEMPHYSICAL_LOW)
? What I see from resource monitor indication of internal physical memory pressure, these are among them:
1. Memory broker shrink operation (e.g. cache cleanup) which can be identified through memory broker ring buffer,
2. Max Server Memory setting change and,
3. Buffer pool stolen page > 75% (as I am not seeing it reflected from single page allocator ring buffer)
But are there others, too? And is there any record/log to identify them through DMV or other mean?
Asked by Travis
(2345 rep)
Apr 10, 2013, 07:31 PM
Last activity: Jan 7, 2025, 12:49 AM
Last activity: Jan 7, 2025, 12:49 AM