Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

enter image description here

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?

share|improve this question

2 Answers 2

First uderstand 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 relase 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 sql server, it's best practice to set the memory limits of SQL if the other app is knw to have variations in memory demand.

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

share|improve this answer

There are two types of memory in use by SQL Server that can be freed: data pages (buffer pool) and 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. Cahces can be trimmed as long as not in use. Buffer pool clean pages can be trimmed immedeatly, 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...

share|improve this answer
    
i've personally not seen days - simply for the fact that i've seen SQL Server hogging needed memory for hours and i get fed up and restart SQL Server. –  Ian Boyd Mar 18 '12 at 12:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.