What's a good way to get SQL Server to release memory to the operating system on a dev machine?

Question

On my dev box I have my SQL Server configured with a high maximum amount of memory (8GB of 16GB). This is required for many development tasks.

Sometimes I want my memory back, though. I don't want to leave 8GB consumed permanently.

What's a good way to get SQL Server to suddenly release all it's memory to the operating system?

DBCC DROPCLEANBUFFERS does not do that. It merely marks clean buffers as available.

Restarting SQL Server is a problem because this causes a 1 minute database recovery to be run due to this bug/feature: Stopping the SQL Server service causes databases to not shut down cleanly (please vote for that connect item; seems like an egregious bug).

Answer 1

I routinely increase and decrease the max server memory configuration option for the SQL Server 2005-2016 instances running on my laptop (with lock pages in memory enabled). Has never caused me any problems, and memory always seems to be released quickly. I also stop, start, and restart instances as I find I need them (using SQL Server Configuration Manager, which I keep open).

As far as checkpoint is concerned, how you configure that depends on version. Manual checkpoint with a duration target has been available since 2005 (though it is undeniably more effective in later versions).

Running a manual checkpoint <duration> per database before shutdown will help spread the I/O load and reduce recovery time on startup. Managed checkpointing will also reduce I/O needed when a memory shrink is requested because there will be fewer dirty pages to write to persistent storage. There is no particular benefit to dropping clean buffers before reducing the max memory setting that I have noticed.

Not using 'lock pages in memory' may increase the rate at which SQL Server can release memory, but I rely on that to avoid paging SQL Server memory to disk. If you don't need it for that purpose, you might choose to run without it.

Answer 2

Sometimes I want my memory back, though. I don't want to leave 8GB consumed permanently.

What's a good way to get SQL Server to suddenly release all it's memory to the operating system?

DBCC DROPCLEANBUFFERS does not do that. It merely marks clean buffers as available.

Since you said you want to free memory on DEV machine so you can use below query

DBCC FREESYSTEMCACHE ('ALL') 
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

But note the above commands won't cause SQL Server process to release memory committed it would just clear caches. That is what you are experiencing.

After running above query you can change SQL Server max server memory to lower value( may be 4-5G in your case) this WILL force SQL Server processes to release memory and thus bring down memory consumption again I am suggesting this as machine in picture is DEV machine. Changing max server memory does not requires restart. The other thing is restarting SQL Server service this will definitely clear caches and memory held by SQL Server process would be released. But after you restart SQL Server, it might, after some time take back all memory.