asked
Make SQL Server give up RAM
I have a SQL Server that is used for ETL and data-warehouse operations storage in the night. But during the day, this server is still consuming 31G of RAM (Total 32G). I am trying to compress some files, but SQL Server won't give up the RAM. Nothing is running on the server at this time. Is there a way for me to tell it "Bad SQL Server" and make it give up the RAM?
SQL Server will consume as much RAM as you allow it using the max memory setting. It will relinquish some if there is pressure from other processes/services on the same server, so not sure what you are doing that isn't 'requesting' the memory. It sounds like you haven't configured a max memory setting, and it would probably be wise to do so. If this is a dedicated SQL Server, a figure of around 28GB should be fine, allowing 6GB for OS and other processes, but it depends on your workload. - [Glenn Berry - Suggested Max Memory Settings for SQL Server 2005/2008][3] - [MSDN - `sp_configure`][1] - [MSDN - Server Memory Options][2] There may also be some pages locked in memory : [1]:
http://msdn.microsoft.com/en-us/library/ms188787.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms178067.aspx [3]:
http://sqlserverperformance.wordpress.com/2008/08/06/suggested-max-memory-settings-for-sql-server-20052008-2/
Are you sure the real culprit isn't Analysis Services, it's not clear if AS is running on your server. It's memory management isn't as smart or robust as SQL Servers. If the OS signals the processes to give up memory SQL Server will try to comply but AS will ignore it and actually gobble up the memory that SQL Server just released. If you stop and restart the OLAP Service is the memory released?
One way to get this accomplished would be to set max memory to a low value, say 4GB, after your ETL and at night bump it back up to 30 GB pre-ETL. Another alternative is to restart SQL Server after ETL.
