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.

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) + ')'
share|improve this question

6 Answers 6

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.

share|improve this answer
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 '10 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 '11 at 13:28

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

share|improve this answer
    
Remember this behavior is 2005 and 2008 - 2000 does not release memory on demand to the OS. –  Paul Randal May 6 '09 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 '09 at 19:35
    
Ah - true - it does the trick of decommitting a little bit of physical memory - you're right! –  Paul Randal May 6 '09 at 20:24

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.

share|improve this answer
up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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?

share|improve this answer
    
Ah ha... yes indeed SQL 2000. –  BIBD May 6 '09 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 '09 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 '11 at 13:25

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.