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
QueryMemoryResourceNotificationto 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
QueryMemoryResourceNotificationto 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?
Best Answer
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.