Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'

Return 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'

XML return,

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

My question is, taken from resource monitor indication of internal physical memory pressure, besides

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)

What are other internal process that may be contributing to internal physical memory pressure that will turn on this indicator = 2 (IDX_MEMPHYSICAL_LOW)? And is there any record/log to identify them through DMV or other mean?

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.