How quickly will SQL Server release RAM when the OS signals a low memory condition?

Question

From the SQL Server 2008 R2 Books Online:

Server Memory Options

When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory. SQL Server uses the memory notification API QueryMemoryResourceNotification to determine when the buffer pool may allocate memory and release memory.

From the SQL Server 2005/2008 Books Online:

Server Memory Options

When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory. Under Microsoft Windows 2000, SQL Server grows or shrinks the buffer cache to keep free physical memory between 4 MB and 10 MB depending on server activity. Maintaining this free memory prevents Windows 2000 from paging. If there is less memory free, SQL Server releases memory to Windows 2000.

Under Windows Server 2003, SQL Server uses the memory notification API QueryMemoryResourceNotification to determine when the buffer pool may allocate memory and release memory.

i understand that SQL Server manages its own memory usage. If the server gets low of free memory, SQL Server releases some memory (starving itself). This is the default, ideal, preferred behavior. But i've seen a lot of situations where SQL Server is not releasing memory when there are other starving applications (i.e. suffering from large amounts of paging faults).

Then i found mention that it can take days for SQL Server to actually release memory:

Professional Microsoft SQL Server 2008 Administration, Memory Usage

SQL Server is one of the best behaved server applications available. When the operating system triggers the low memory notification event, SQL will release memory for other application to use, actually starving itself of memory if another memory-greedy application is running on the machine. The good news is that SQL will only release a small amount of memory at a time, so it may take hours, and even days, before SQL will start to suffer. Unfortunately, if the other application desperately needs more memory, it can take hours before SQL frees up enough memory for the other application to run without excessive paging.

Does this sound right? SQL Server is a well-behaved server application that will cripple a server for days?

Is there an option (aside from setting an upper-limit on SQL Server memory usage) to speed up the rate at which SQL releases memory?

Answer 1

First understand how Windows memory works (see How do I tell if my Windows server is swapping?).

Now that we know paging isn't all bad, SQL Server won't release memory fast enough for other applications on Windows that have large memory spikes to be satisfied in what we would consider a reasonable amount of time.

It will not take "days", it would take days for SQL to suffer; not the other app. It's the other app that will take hours to get enough free pages in memory to run at an acceptable rate (presuming a large memory page request).

In general if other apps have to exist on a machine with SQL Server, it's best practice to set the memory limits of SQL if the other app is known to have variations in memory demand.

I do love the quote about SQL Server being "well behaved". It is, in fact, well behaved; it just takes forever (relatively speaking) to do so.

Answer 2

There are two types of memory in use by SQL Server that can be freed:

• data pages (buffer pool)
• caches

As long as the low memory situation is being signaled, both will be aggressively trimmed.

The caches are described in Q and A: Clock Hands - what are they for?
The buffer pool mechanism is described in Buffer Management

• Caches can be trimmed as long as they are not in use.
• Buffer pool clean pages can be trimmed immediately (dirty pages need to flush to disk first)

All in all, SQL Server can release all memory that can be immediately released (clean pages, unpinned caches) in a matter of seconds. Dirty pages can be freed as soon as the flush IO completes. I have no idea where where would the idea of 'it takes days' came from...