question

Raj More avatar image
Raj More 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-2008-r2windows-server-2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Nice thought Scot!
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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/
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
whoops HAHA...been on the cooking sherry again!
1 Like 1 ·
Tim avatar image Tim commented ·
Great response. You beat me to this. I am sitting on SQL Skills training listening to Paul Randal. So many folks forget to put in the max memory setting and allow SQL to consume ALL memory.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
erm... 28+6 = 34, not 32... But, yeah, good response.
0 Likes 0 ·
Scot Hauder avatar image
Scot Hauder answered
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?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.