Sample Header Ad - 728x90

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