Sql-server – SQL Server Internal Memory Pressure

database-internalsmemorysql server

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):

<ResourceMonitor>
  <Notification>RESOURCE_MEMPHYSICAL_LOW</Notification>
  <IndicatorsProcess>2</IndicatorsProcess>
  <IndicatorsSystem>0</IndicatorsSystem>
  <NodeId>0</NodeId>
  <Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>
  <Effect type="APPLY_HIGHPM" state="EFFECT_IGNORE" reversed="0">100789</Effect>
  <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>
</ResourceMonitor>

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:

<Pressure status="0"><AllocatedPages>477</AllocatedPages>
  <AllAllocatedPages>477</AllAllocatedPages>
  <TargetPages>31553</TargetPages>
  <AjustedTargetPages>31553</AjustedTargetPages>
  ...
</Pressure>

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?

Best Answer

The closest internal documentation I can find on the RM is from the PSS team here: How It Works: What are the RING_BUFFER_RESOURCE_MONITOR telling me?

Which specific to your question on which internal processes may contribute to changing the indicator there is no specific documentation that answers this...that I can find. The article references actually shows that there is a startup trace flag 8020 that can be used (with extreme caution) to narrow down what is triggering RM.

I think the main bit of text from the article above would be this:

There are memory broker decisions that come into play at this time. What the information is is saying is that a cache or the cache’s predicted usage will exceed internal targets and we need to start doing cache cleanup. By looking at the memory broker, ring buffer entries, you can see the behavior that broker it asking of RM. Using the clock hands you can see the internal and external hand movement. This will help you determine which caches are involved. The dbcc memorystatus() show good details and last notification states as well.

Not sure if that answers the question in full, but is about as close to what I can find online.

Related Question