10

I have an SQL Server instance who's memory usage gradually grows until windows will give it no more. It seems logical that the occasional big query result would cause the instance to grow.

Is there a way I can convince SQL Server to release the memory it doesn't need any more (other than restarting the service)?

Edit:
I'm using SQL Server 2000 SQL Server 8.00.2039 - SP4 (Standard Edition)

I was able to find that out using the following query:

SELECT 'SQL Server ' 
    + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' 
    + CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' (' 
    + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

7 Answers 7

19

This is exactly how SQL Server is supposed to work.

If you have other services on that machine and don't want SQL to consume all the available memory, you will need to set the maximum server memory. See SQL Server Memory Options on MSDN.

2
  • 1
    Sounds more like a bug than a feature...its happening to me to. SQL Server takes up 95% of available RAM to do something, and fails to release it when it is complete.
    – Alchemical
    Nov 7, 2010 at 20:54
  • 1
    @Alchemical Depends what you mean by "complete". SQL Server can make use of all RAM in a computer; using it as a disk cache. It doesn't "complete" using it as a disk cache until you are done using SQL Server.
    – Ian Boyd
    Jul 8, 2011 at 13:28
8

The other posters are correct that this is by design, but you absolutely want to limit the max memory to a bit less than your server's RAM. Think about this sequence of events:

  • SQL 2000 runs happily, consuming all of your server's RAM
  • Someone RDPs in, or you have to pull up IE to download a patch, or your backups kick off, whatever
  • SQL has to deallocate and free enough memory for the OS to work
  • Performance sucks while it's freeing memory and paging to disk
  • Things go well enough once it's stable
  • The other operation completes and SQL gradually reclaims the freed RAM
  • Repeat

To avoid this, configure your max server memory limit to something around 80-90% of your actual physical memory. Instructions for SQL 2000 at: http://msdn.microsoft.com/en-us/library/ms178067.aspx

3
  • Remember this behavior is 2005 and 2008 - 2000 does not release memory on demand to the OS. May 6, 2009 at 19:06
  • 2
    Not exactly on demand, but it does release memory back to the OS. From the link I posted: " 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. If there is more memory free, SQL Server allocates memory to the buffer pool."
    – sh-beta
    May 6, 2009 at 19:35
  • Ah - true - it does the trick of decommitting a little bit of physical memory - you're right! May 6, 2009 at 20:24
4

It will only release it if the OS signals that it is RAM starved, or if you stop and restart the service; the thing to do is limit the max amount SQL will use by configuring the 'max server memory' value. If there's nothing else on the server that needs the RAM (and hopefully there isn't) I wouldn't worry about it.

4

So, to summarise the answers:

There is no way to prompt MS SQL Server to release memory it doesn't immediately need. SQL Server should automatically releasing memory when it required, but not before then. And if your are having memory issues, you should reduce the value of the "max server memory" memory optin.

3

SQL Server will consume memory and not give it back unless it is told by the operating system that there is memory pressure. As Portman has indicated, this is by design, and if you want to limit the memory consumption, you need to set the maximum server memory SQL Server will use.

2

Remember that the behavior you're all describing is from SQL Server 2005 onwards, when the memory manager was rewritten to (among other things) respond to memory pressure requests from the OS.

For SQL Server 2000 and before, once it grabs memory it won't give it back, no matter how much the OS shouts for it.

CodeSlave - are you running on 2000, 2005, or 2008?

3
  • Ah ha... yes indeed SQL 2000.
    – BIBD
    May 6, 2009 at 14:40
  • 3
    It doesn't give it back when the OS shouts for it, but it DOES give it back if it sees that the OS is below a certain threshold for free physical memory. See msdn.microsoft.com/en-us/library/ms178067.aspx
    – sh-beta
    May 6, 2009 at 19:36
  • And to reinforce what sh-beta said, SQL Server 2000 only releases memory when it notices that the OS is low on physical memory because it wasn't until Windows XP (2001) that Windows added an API to allow "the OS to shout for it" (msdn.microsoft.com/en-us/library/aa366799(v=vs.85).aspx) . SQL Server 2005 takes advantage of that new feature of Windows. But when SQL Server 2000 was written there was no way for the OS to shout for it, forcing SQL Server to periodically check for memory pressure.
    – Ian Boyd
    Jul 8, 2011 at 13:25
0

Old question, I know, but a way of forcing (newer, at least) SQL to release memory is to write an application allocating as much memory as it can in chunks, waiting for (say) 15 seconds (e.g. Sleep(15000)) and the freeing the allocated memory and exiting; I tried this and SQL does release the memory so the system gets back its RAM; writing code like the above is almost trivial using C/C++, just a matter of setting up a chain of stucts to hold the memory block chain (pointer and size), progressively reduce the size when a "malloc()" fails until it reaches a minimum (say less than 1024) and then traverse the linked list to free back the allocated blocks

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .