Reclaiming memory from SQL Server

Question

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) + ')'

Solution 1

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.

Solution 2

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.

Solution 3

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

Solution 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.

Solution 5

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.

View more solutions

Comments

[BIBD]

Ah ha... yes indeed SQL 2000.

[Paul Randal]

Remember this behavior is 2005 and 2008 - 2000 does not release memory on demand to the OS.

[sh-beta]

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]

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

[Paul Randal]

Ah - true - it does the trick of decommitting a little bit of physical memory - you're right!

[Alchemical]

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.

[Mark]

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.

[Mark]

@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.