SQLOS's memory manager: responding to memory pressure

Artigo
02/19/2005

Resource Monitor and Memory Pressure

When configuring SQL Server it is very important to understand how it reacts to memory pressure. I have already spent significant amount of time describing types of memory pressure. In this post you will understand why it is important. Memory pressure is categorized into two major groups: VAS and physical. Physical memory pressure could be imposed by OS, we called external or it could be imposed by the process itself we call it internal.

SQLOS implements a complete framework to enable process's handling any type of memory pressure. In the heart of the framework lies Resource Monitor task, RM. RM monitors state of the external and internal memory indicators. Once one of them changes, RM observes state of all indicators. Then it maps indicator's states into corresponding notification. Once notifications is calculated it broadcasts it to memory clerks.

` ------------------

| Resource Monitor|

/ ------------------\

/ | \

------------------------------ ------------- ------------------------------

------------------------------ ------------- ------------------------------

Resource Monitor and Memory Clerks

Remember SQLOS has two types of nodes: memory node and cpu nodes. Memory nodes provide locality of allocations and cpu nodes provide locality of scheduling. Currently every cpu node has its own resource monitor. The reason is to be able to react to memory pressure on a given node - I will talk more about cpu nodes when covering SQLOS scheduling subsystem. For now remember that depending on machine configuration there could be multiple RM tasks running at the same time.

Large memory consumers leverage memory clerks to allocate memory. One more important task of memory clerks is to process notifications from RM. A consumer can subscribe its clerk to receive memory pressure notifications and react to it accordingly.

Every cpu node has a list of memory clerks. First RM calculates notification it needs to send. Then it goes through the list and broadcast notification to each memory clerk one by one. During the broadcast caches receive notification as well since they are memory clerks.

------------------

------------------------| Resource Monitor| -------------------------

/ ------------------ \

/ / \ \

--------------------- ----------------------- -------------------------- ------------------

--------------------- ----------------------- -------------------------- ------------------

From RM's scheduling point of view there are couple of important points you need to be aware.

Resource Monitor runs on its own scheduler, we called it hidden scheduler
Resource Monitor runs in non-preemptive mode.
DAC node doesn’t have its own Resource Monitor

There are several memory clerks that can respond to memory pressure. We already talked about caches. In addition every cpu node leverages its clerk to trim worker and system thread pools under memory pressure. Full text leverages its memory clerk to shrink shared memory buffers it shares with MSSearch. CLR uses its clerk to trigger GC. Buffer pool leverages its clerk to respond to external and VAS memory pressure only. (Why?)

External Memory pressure: RM and Buffer Pool

From SQLOS perspective Buffer Pool is a single page allocator - extensively used memory manager. External memory pressure is signaled by Windows. RM wakes up and broadcasts corresponding notification to clerks. Upon receiving the notification BP recalculates its target commit, amount of physical memory BP is allowed to consume. Keep in mind that target commit can't be lower than configuration parameter specified through sp_configure min server memory and can't be higher of max server memory. If new target commit is lower than currently committed buffers, BP starts shrinking until external physical memory pressure disappears. During this process BP tries to decommit or in case of AWE free physical memory back to OS. Remember that in SQL2000 BP didn't react to physical memory pressure when running in AWE mode.

Internal Memory Pressure: BP and Resource Monitor

Shrinkage of BP causes internal memory pressure. This is one of the ways for BP to get process into internal physical memory pressure What components BP notifies about internal memory pressure? Yes, you guessed correctly, SQLOS exposes a mechanism for BP to turn on RM's indicator corresponding to internal memory pressure. As you learned RM translates the indicator's signal to notification it will broadcast to clerks. BP has its clerk and will get RM's notification back. Oh no, we get into infinite loop!? Actually this is not the case because BP only monitors external physical memory pressure. It ignores internal physical memory pressure altogether.

There are couple other ways for internal physical pressure to appear. It could be caused by dynamically changing max server memory. In addition it could raise when 75% of BP's pages are stolen using SQLOS's single page allocator interface. By triggering internal physical memory pressure BP reclaims its pages from caches and other components currently consuming them.

VAS Memory Pressure

So far I discussed how SQLOS and consequently SQL Server handles physical external and internal memory pressure. Handling VAS pressure is harder because on Windows it is difficult to recognize it. There are two ways how RM gets notified about VAS pressure. The first way is for memory node to notify RM. When memory node Virtual or Shared memory interfaces fail to allocate a region of 4MB and below (RM doesn't get notified if size of a region above 4MB), memory node turns on RM's VAS low indicator. There also exists proactive way, when RM is running it probes VAS for 4MB size if such region no longer exists RM itself turns on VAS low signal and starts broadcasting corresponding notification.

Responding to VAS pressure is what makes Yukon different from SQL2000. In SQL2000 for server is hard to recover once it gets into VAS pressure. In Yukon VAS pressure notification will be send to all memory clerks so they have opportunity to shrink. For example cpu node will shrink its threads, CLR might unload appdomains that currently not in use, network libs will shrink their network buffers.

You remember, when talking about SQLOS memory manager, I mentioned that in AWE mode BP is capable of reacting to VAS pressure? Here it all comes together. When BP receives VAS low notification it enumerates its 4MB VAS regions it reserved previously. If it finds 4MB region that is not currently in use or either used by database pages it can easily free it.

Monitoring memory pressure:

The subject won't be complete without taking a look at how one can monitor, diagnose, different types of pressures SQL Server gets exposed to. Yes, we made yours and our life simpler. There is dmv that you can take a look at to find out history of memory pressure.

Following query shows a set of last notification RM broadcasted:

select * from sys.dm_os_ring_buffers

where

ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR'

(yes we have several different ring buffers that you can pick into :-), including schedulers, exceptions and OOMs, but these are subjects for different posts)

Here is the example of query output.

<Notification>RESOURCE_MEMPHYSICAL_HIGH</Notification>

</ResourceMonitor>

</MemoryNode>

</MemoryRecord>

</Record>

Following query shows when BP, single page allocator, turns on/off internal memory pressure

select * from sys.dm_os_ring_buffers

where

ring_buffer_type='RING_BUFFER_SINGLE_PAGE_ALLOCATOR'

</Pressure>

</Record>

Sorting the outputs from these two queries by time will allow you to observe the actual behavior of the SQL Server over time with respect to memory pressure.

If you are a careful reader most of the output from ring buffer queries should make sense to you by now. Some time latter on I will try to spend more time on detailed description of the output.

Conclusion:

Memory pressure might significantly impact server performance and stability. Especially when SQL Server shares a box with other applications or shares its VAS with xps or CLR. Memory pressure might triger extra I/Os, recompilies, and other unnecessary activities. Understanding and diagnosing the types of memory pressure SQL Server is exposed to is very important part of managing your server and writing applications for it. I hope the information provided in this post will enable you to do your job more efficiently.

Have a good weekend!

Comments

Anonymous
February 20, 2005
Just a quick note to let you know that you should keep on writing this stuff. While at the moment it's mostly academic for me, it's nice to know about the underlying theory of the way Yukon handles stuff.
Anonymous
March 24, 2006
There are two important memory considerations you may want to track when using SQL CLR functionality:...
Anonymous
November 21, 2006
Checklist for time out errors Memory pressure : In most cases timeouts are caused by insufficient memory
Anonymous
November 22, 2006
Checklist for time out errors Memory pressure : In most cases timeouts are caused by insufficient memory
Anonymous
March 09, 2007
PingBack from http://www.kodak-camera.info/blog/189/two-principal-methods-of-setting-the-sql/
Anonymous
April 18, 2007
Recently a customer wanted to know some details on how SqlServer manges memory, sort of the basics. Presented
Anonymous
October 02, 2007
A year and a half ago I wrote about the undocumented Dynamic Management View sys.dm_os_ring_buffers in
Anonymous
December 09, 2007
PingBack from http://sqlserverpedia.com/blog/?p=201
Anonymous
March 06, 2008
Have you seen the above message within the SQL Server error logs? If not then no need to worry and make
Anonymous
August 05, 2008
The comment has been removed
Anonymous
August 05, 2008
The comment has been removed
Anonymous
August 09, 2008
PingBack from http://www.sqlserverfeeds.com/756/memory-usage-in-sql-clr-2/
Anonymous
March 10, 2009
把和SQLServer内存和AWE相关问题的基本内容整理了一下。 1.SQLServer内存基本概念在涉及SQLServer内存时有几个比较重要的术语： BufferPool(BP...
Anonymous
June 18, 2009
PingBack from http://homelightingconcept.info/story.php?id=2137

Compartilhar via

SQLOS's memory manager: responding to memory pressure

Comments

Recursos adicionais