Sql-server – External Memory Pressure

memorysql serversql-server-2008-r2

I have a few "gaps" in my understanding of external memory pressure. I've found quite a bit of helpful information online, including this info from SQLSkills.com.

SQL Server has a built-in component of the SQLOS, the Resource
Monitor, that monitors the QueryMemoryResourceNotification Windows
Server API to get status about the OS level memory availability on the
server. If the Windows OS is under memory pressure, it will set a low
memory notification that the Resource Monitor thread will detect and
force the external clock hands on the caches internally to begin
sweeps of the caches to cleanup and reduce memory usage allowing the
process to free memory back to the OS.

I have inferred that the Windows OS does not actively "push" the OS
level memory availability status to SQL Server. Rather, the SQLOS
Resource Monitor must actively ask for it via the Win API
function.
This would seem to be an asynchronous operation. Am I interpreting
this correctly?
If my reasoning for #1 is sound, what causes the Resource Monitor to get the OS level memory availability status? Is is performed on a schedule? Event driven? How much time might pass from the moment there is low memory at the OS level to the moment the SQL OS Resource Monitor becomes aware of it?
When SQL Server reduces memory usage to free memory back to the OS, just how bad is that? Is this an "expensive" operation that should be avoided at all costs?

Best Answer

Yes you are correct, from SQL Server 2005 onwards with arrival of SQLOS( actually it was called UMS in SQl Server 2000) Wiindows OS never directly asks SQL Server to trim down its memory consuption when OS is seeing memory pressure. Instead it raises flags(MEMPHYSICAL_HIGH and MEMPHYSICAL_LOW). These flags are monitored by SQLOS dedicated threads ,that in turn sets a internal High Memory resource flag that tells the various caches they can allocate additional memory, or Low Memory resource flag that tells the various caches to try and reduce their memory allocations.

If memory pressure is severe apart from raising low memory notificaion OS can page out SQL Server as well. There are certain scenarios where things would work differently regarding paging. No paging would happen if SQL Server service account has LPIM. In case of LPIM VirtualAlloc() function do not do major chunk of memory allocations instaed AWEAPI() does the major memory allocation and this is non pageable memory

The monitoring is continuous this is one of the tasks that SQLOS dedicated threads do. If I say dedicated they are bound to keep looking for any notification. I am not aware about schedule if any but whatever may be the schedule it would be definitely in seconds.The reaction time is quick and moment SQL server sees low memory notification it would immediately start trimming its memory consumption. But sometimes if external memory pressure is grave SQL Server might not get enough time to trim its memory consumption and free its caches and before it can complete it, OS might page out SQL server process.
Yes its expensive in terms that SQL Server has to clear its caches, trim down its memory consumption basically devoiding its processes from memory Or partially catering to there memory requests. The process of consuming and releasing memory involves cost so once SQL server consumes memory it does not gives it back(unless OS flags low memory notification) because it has to go all the way to process of asking memory from OS and the VirtualAlloc() function of OS would find a VAS region and map it to physical memory to actually provide memory to the process.

Edit:

I'm wondering about the ramifications of not setting Max Server Memory.

Microsoft suggests that if you have DEDICATED SQL Server box solely running SQL Server database engine services leave max server memory to default. SQL Server can manage in better way. If you take my opinion this is not true if you have Windows server 2008 and 2005 having SQL Server 2005-2008 R2 installed on it because of various Bugs in Windows server release trimming SQL Server memory consumption. This holds true if you have latest Windows server 2012 having SQL Server 2012. But again this is as per my experience and scenario is limited.

What I suggest is its better to fix max server memory if you correctly set optimum value. Whatever version you are in please see answer given(By me and others) on This SE Thread and This Thread there is lot of evidence to say you should fix max server memory.

If SQL Server "knows" the OS is low on memory immediately, and can return memory to the OS in a timely fashion, why not let it perform that task

That is good question but like I wrote before sometimes if memory crunch faced by OS is too severe SQL Server will respond to memory pressure but in that case before SQL Server gets enough time to clear cache it would be paged out by OS(because OS is in desperate need of memory) eventually slowing SQL Server process very much. Cases where OS can face severe memory crunch

Bug in OS
Third party driver installed leaking memory
Max server memory not set correctly and point 1 and 2 are true.
Someone start big file copy and any of the above 3 points are true.

In all above case problem was with OS and applications installed on it and SQL Server became victim.

Related Solutions

Sql-server – How to free SQL Server memory

Stop/start the service, nothing else will release the memory back to the OS.

Obviously not something you'd ever want to do with an operational server but perfectly reasonable for a local sandbox. With 3 different instances on my laptop, its the only viable way.

Edit following @Nick's comment.

As James noted, if it reserves more than the minimum you specify, it should in theory release it when it doesn't need it and there is memory pressure on the machine.

What's your definition of "doesn't need it"? Once the buffer pool has been filled, it isn't going to drop pages out until they become disfavoured by other pages being brought in. If you want the memory back immediately, as is the case for @jrara, you've got to stop/start.

Sql-server – SQL Server memory usage

There are other memory consumers outside of the buffer pool. For instance, SQL Server will use memory for worker threads, multi-page allocations, linked servers, extended stored procedures, CLR, etc.

The buffer pool is never going to equate to 100% of memory consumed by SQL Server. It's a common misconception, as well as setting max memory will be the actual max memory SQL Server uses (not true).

EDIT: Here's how I think you could get non-buffer pool memory size. Note: wait for the input of other SQL Server professionals before taking this query as fact. I cannot guarantee that it is absolutely correct.

declare 
    @data_cache_size_mb decimal(12, 2),
    @plan_cache_size_mb decimal(12, 2),
    @total_mem_mb decimal(12, 2)

select
    @data_cache_size_mb = count(*) * 8 / 1024.
from sys.dm_os_buffer_descriptors

select
    @plan_cache_size_mb = sum(size_in_bytes) / 1024. / 1024.
from sys.dm_exec_cached_plans

select
    @total_mem_mb = sum(pages_allocated_count) * 8 / 1024.
from sys.dm_os_memory_objects

select
    @data_cache_size_mb as data_cache_size_mb,
    @plan_cache_size_mb as plan_cache_size_mb,
    @data_cache_size_mb + @plan_cache_size_mb as buffer_pool_size_mb,
    @total_mem_mb as total_instance_mem_mb,
    @total_mem_mb - (@data_cache_size_mb + @plan_cache_size_mb) as non_buffer_pool_size_mb

Best Answer

Related Solutions

Sql-server – How to free SQL Server memory

Sql-server – SQL Server memory usage

Related Question