Home / server / Questions / 108192
Accepted
Rob Farley
Asked: 2010-02-01 21:29:41 +0800 CST

How do I get SQL Server to release its memory?

  • 772

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.

It's potentially a duplicate of both SQL Server not releasing Memory and Reclaiming memory from SQL Server, but I'm wondering if there's a different answer. 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...

windows sql-server memory

5 Answers

  1. Best Answer

    I believe your only option is to restart the SQL Service, or to run an application that consumes lot's of memory to force SQL to release it.

    I like @Nick Kavadias's suggestion: monitor the Memory: Page Faults/Sec performance counter to show that SQL has not put the OS's memory under presssure.

    • 12

  2. One way to show them that it will do that is to have SQL Server chew up all the memory. Then copy a huge file over the network (large SQL Backup files work great for this). This will cause the system cache to fill, and Windows will begin asking SQL for its memory back. SQL will begin returning memory back to the OS until it reaches your min memory setting.

    (Sorry didn't get the entire question via Twitter, more than 140 characters were needed apparently.)

    • 5

  3. You mention 'cubes' so it's not clear whether you talking about AS engine or the relational engine. The relational engine can free memory on demand:

    DBCC FREESYSTEMCACHE('<cache name>');

    Where 'cache name' is taken from sys.dm_os_memory_clerks. The spec of DBCC FREESYSTEMCACHE only mentions governor pools, but many more caches can be evicted actually.

    But if all the memory is used by the buffer pool, then evicting the entire buffer pool will result in tremendous performance degradation and huge IO load. Better let SQL handle this itself.

    • 2

  4. With both the AS and the DS engines you can put a cap on the memory so they stay below a safe threshold; this is recommended on 64 bith machines with lots of RAM and/or multiple instances of SQL. For at least the database engine if you change the memory cap to below what the current usage it will reduce the amount of memory on the fly w/o having to restart the service, not sure about SSAS though.

    • 2

  5. The easiest I have found is to go to server memory properties, enter a smaller value, apply, wait a few minutes and adjust the config back up.

    We have also found the throttling sql servers memory usage can actually improve the performance of the server as it gives windows more ram for caching, etc

    • 1