How do I get SQL Server to release its memory back to Windows?

Question

question

Rob Farley asked Feb 1, '10

How do I get SQL Server to release its memory back to Windows?

I know SQL likes RAM, but releases it when Windows asks. I know this is how SQL is supposed to work. But I have a Windows Administrator who isn't convinced that SQL will actually return the RAM, and on this particular (virtual) server, SQL doesn't need much once Analysis Services is finished, but there's nothing else on this server that's bothering to ask SQL for much.

So I'm trying to reassure the Windows Admins that problems with the virtual environment are not "because SQL is using too much RAM", but I can't seem to persuade SQL to release it without restarting the service.

When processing cubes, the SQL service happily takes 8GB of RAM, but because there's no pressure on it, it doesn't release much of this during a normal day. The Windows guys scream, and it would be much nicer to have SQL release this.

I don't want to use the max memory setting, because I do actually want SQL to use that much RAM when processing. I just want it to go back down again afterwards.

Waiting for Windows to reclaim it isn't going to persuade the Windows guys here. Restarting the service is an option, but I'm really not a fan of that idea.

I want to know how to get Windows to ask for it back...

I've also asked this on ServerFault...

http://serverfault.com/questions/108192/how-do-i-get-sql-server-to-release-its-memory

memory windows

10 |1200

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

Answer 1 · 2010-02-01T06:08:39Z

sp_lock answered Feb 1, '10

I came across the article below when doing some research into SQL memory utilization. Some interesting point!

http://piglings.blogspot.com/2009/10/sql-server-2008-memory-leak.html

10 |1200

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

Answer 2 · 2010-02-01T05:31:47Z

Fatherjack answered Feb 1, '10

Flipant answer: Install Photoshop!?

10 |1200

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

Answer 3 · 2010-02-01T06:04:47Z

Steinar answered Feb 1, '10

You could set up 2 jobs, one to allocate 8 GB mem to SQL before the cubes are processed, and one to scale back on mem to say 2 GB when the processing is finished:

Job 1

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'8000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

Job 2

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'2000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

10 |1200

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

Answer 4 · 2010-12-16T05:57:07Z

mevenson answered Dec 16, '10

try running this for a while in the debuffer - stop it and check your memory with Task Manager. using System; using System.Collections; using System.Collections.Generic; using System.Text; namespace GetMemoryBack { class Program { static void Main(string[] args) { int i = 1; int j = 1; List > a = new List >(); for (i = 1; i < 1000000; i++) { List b = new List (); for (j = 1; j < 1000000; j++) { try { b.Add(0); } catch (Exception e) { Console.WriteLine(String.Format("{0} after {1} Adds", e.Message, i * j)); } } a.Add(b); } } } }

10 |1200

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

question