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?

more ▼

asked Dec 05, 2011 at 11:26 AM in Default

avatar image

Raj More
1.8k 83 89 90

Nice thought Scot!

Dec 05, 2011 at 12:12 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

There may also be some pages locked in memory : http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

more ▼

answered Dec 05, 2011 at 11:38 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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.

Dec 05, 2011 at 11:52 AM Tim

erm... 28+6 = 34, not 32... But, yeah, good response.

Dec 05, 2011 at 01:03 PM ThomasRushton ♦♦

whoops HAHA...been on the cooking sherry again!

Dec 05, 2011 at 02:27 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Dec 05, 2011 at 11:52 AM

avatar image

Scot Hauder
6.5k 13 16 22

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 06, 2011 at 05:12 AM

avatar image

12.1k 30 36 42

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 05, 2011 at 11:26 AM

Seen: 4129 times

Last Updated: Dec 05, 2011 at 11:26 AM

Copyright 2018 Redgate Software. Privacy Policy